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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.