Hi,
This is a data manipulation exercise.
How do I do merge every row in the right data set (have2) with only the rows that have the yesno variable="Y"? I want to include all the rows in the left data set that have either yesno="Y" and yesno="N".
Thanks
data have1;
infile datalines dsd dlm=",";
input id $ visitnum yesno$;
datalines;
001, 1, N
001, 2, Y
002, 1, N
002, 2, Y
003, 1, Y
003, 2, Y
;
run;
proc sort; by visitnum; run;
data have2;
infile datalines dsd dlm=",";
input id $ visitnum howmany seq;
datalines;
001, 2, 10, 1
001, 2, 15, 2
002, 2, 10, 1
002, 2, 15, 2
003, 1, 10, 1
003, 2, 15, 1
003, 2, 15, 2
;
run;
proc sort; by id visitnum; run;
proc sql; /*incorrect code attempt*/
create table want as
select a.*, b.howmany, b.seq
from have1 a
left join have2 b on a.id=b.id and b.visitnum=b.visitnum
where yesno="Y";
quit;
desired output:
proc sql;
create table want as select
a.*,
case when a.yesno='Y' then b.howmany else . end as howmany,
case when a.yesno='Y' then b.seq else . end as seq
from have1 as a left join have2 as b
on a.id=b.id and a.visitnum=b.visitnum;
quit;
I don't understand the goal:
How do I do merge every row in the right data set (have2) with only the rows that have the yesno variable="Y"? I want to include all the rows in the left data set that have either yesno="Y" and yesno="N".
It seems the first sentence (where you want yesno="Y") is contradicted by the second sentence (where yesno can be either "Y" or "N").
I don't know if it's possible. But i would like to merge the values from have2 with only the values from have1 where it meets the condition that yesno="Y", else if yesno is not "Yes", then don't merge.
The final table i would like to have both the values of yesno (either yesno="Y" or yesno="N" while the yesno="N" values have missing values where the merge would have been)
The desired output at the bottom of the OP will probably show more than i can explain.
proc sql;
create table want as select
a.*,
case when a.yesno='Y' then b.howmany else . end as howmany,
case when a.yesno='Y' then b.seq else . end as seq
from have1 as a left join have2 as b
on a.id=b.id and a.visitnum=b.visitnum;
quit;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.