topic How do I do a conditional merge with a left join to include all the rows from the left data set? in SAS Programming

How do I do a conditional merge with a left join to include all the rows from the left data set?

Hello_there — Tue, 24 Jan 2023 20:40:38 GMT

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:

Re: How do I do a conditional merge with a left join to include all the rows from the left data set?

PaigeMiller — Tue, 24 Jan 2023 20:31:36 GMT

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

Re: How do I do a conditional merge with a left join to include all the rows from the left data set?

Hello_there — Tue, 24 Jan 2023 20:35:58 GMT

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.

Re: How do I do a conditional merge with a left join to include all the rows from the left data set?

PaigeMiller — Tue, 24 Jan 2023 20:50:39 GMT

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;

Re: How do I do a conditional merge with a left join to include all the rows from the left data set?

Hello_there — Tue, 24 Jan 2023 20:52:52 GMT

Thanks, PaigeMiller!