Weird! Eh for some reason does not work 1

[saqi2000]

When I try to use the SQL below it does not work for some reason.

proc sql;
create table as work.myemployees
Select empid, count(*)as num_of_shifts
from work.employees
group by empid
having count(*) >1;

quit;

But if use the same sql but without try to create table it works.

proc sql;
Select empid, count(*) as Numer_of_shifts
from work.employees
group by empid
having count(*) >1;

quit;

Could you put me in the right direction please?

Thanks

Saqi

 

[MatthiasB]

Hi Saqi,

don't know if just a typo in your example or if you copied directly from program editor - try swapping the 'as' and the dataset:

create table work.myemployees as ...

apart from that quick-looks good to me. What does the log say if it's not that?

Cheers,
Matthias

 

[saqi2000]

Matthias,

thanks very much. I think it was a long day yesterday that's I could not notice it.

I would like only the candidates which appear in one table but not in other.

data work.staff ;
merge work.staff1(in=st1)
work.staff2 (in=st2);
by id;
if st1 and not in st2;
run;

I only want the observations from staff 1 which are not in staff 2. I have got 20 members of staff in staff1 and 15 members of staff is staff 2 data set. 15 are exactly same in both data sets but 5 of them which are in data set staff they are not in staff2. So how can I obtain them.

Thanks

Saqi

 

[MatthiasB]

Hi again!

drop the in from the "if" statement. The "in" is set oriented, e.g. "... where id in (1, 2, 3) ...". Should look like this:

if st1 and not st2;

With the merge you must take care of order of merged datasets, it overwrites right to left: if you have the same variable on both tables and their content is different, the result dataset contains the value from th most-right dataset.

Cheers,
Matthias