BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
himalayancat
Fluorite | Level 6

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

2 REPLIES 2
ballardw
Super User

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
Ammonite | Level 13

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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