BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Hello_there
Lapis Lazuli | Level 10

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:

Hello_there_0-1674592052674.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Hello_there
Lapis Lazuli | Level 10

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.

PaigeMiller
Diamond | Level 26
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;
--
Paige Miller

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1088 views
  • 1 like
  • 2 in conversation