keep statement in proc sql

[himalayancat]

Hi all,

i saw someone put keep statement in proc sql.i tried to replicate and it gives me values empty.

1.is it even possible to put "keep" statement in proc sql!! 

2.why are values in Weight field coming empty!!

2.how should i correct it!!

proc sql;
create table age11_12 as
select name,AGE from sashelp.class
where age in (11,12);
quit;
/**/
proc sql;
create table not11_12 as
select * from sashelp.class
where age NOT in (11,12);
quit;
/**/
proc sql;
create table aa as
select aa.*,
bb.Weight
from age11_12 aa
left join
not11_12 (keep= Age Weight) bb
on
aa.Age=bb.Age;
quit;

*********i have attached the result *************

so it runs but bb.Weight are coming empty.

please suggest!

Thank you,

[ballardw · Accepted solution]

Your problem has nothing to do with "keep".

Because of the way you created the sets age11 and not11_12  there are NO ages in common for this. You even named the datasets to reference the content difference.

on
aa.Age=bb.Age;

to match on. If you reverse the order of the tables in the join you will get weights with the other variables from AA as missing.

The correction would be to match on another variable if available, like SEX but don't exclude it from BB.

This works just fine:

proc sql;
   create table work.junk as
   select *
   from sashelp.class(keep=sex age)
   ;
quit;

so yes keep works though is not preferred as it is non-standard SQL and would not work for pass-through code to a remote DBMS.

[ChrisBrooks]

Firstly yes you can use drop and keep statements in Proc SQL - it sometimes helps in reducing the amount of typing you have to do if you have files with lots of variables in.

Secondly the reason weight is missing is that a left join will return all records from the left table (aa) and only matching records from the right table (bb). In your case there are no matches because your first two Proc SQL statements creates files with ages 11-12 and without ages 11-12 and as the weight variable is not included in table aa it is created with missing values. In fact your final SQL statement is pointless as there never will be any matches......