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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1121 views
  • 1 like
  • 2 in conversation