DATA Step, Macro, Functions and more

keep statement in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

keep statement in proc sql

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,

 


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 13,550

Re: keep statement in proc sql

Posted in reply to himalayancat

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.

 

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 13,550

Re: keep statement in proc sql

Posted in reply to himalayancat

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.

 

Valued Guide
Posts: 593

Re: keep statement in proc sql

Posted in reply to himalayancat

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......

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 58 views
  • 0 likes
  • 3 in conversation