topic Re: data step merge issue in SAS Programming

data step merge issue

Niugg2010 — Fri, 29 Apr 2022 14:20:42 GMT

I met a strange issue when I merge two datasets. Below is a simple example


data a;
subjid="101-001";
do visit='vis01', 'vis02';
do param="aaa", 'bbb', 'ccc';
output;
end;
end;
run;
data b;
subjid="101-001";
do visit='vis01', 'vis02';
flag="Y";
output;
end;
run;
proc sort data=b; by subjid visit; run;
proc sort data=a; by subjid visit; run;

data c;
merge a b;
by subjid visit;
if param='aaa' then call missing(flag);
/* if visit='vis01' then call missing(flag);*/
run;

I merged a and b. I hope only when param='aaa' then flag= missing. but if I run above code, all flag values are missing. I know if I do two data steps, I can resolve this issue. But I am not sure why this happened, and how I can avoild this issue. Please help me out. Thanks

George

Re: data step merge issue

Kurt_Bremser — Fri, 29 Apr 2022 14:28:41 GMT

For each match, the dataset b is read only once (as there is only one observation for each BY group). After that, the value you set is retained throughout the group. As an exercise, set flag to some non-missing value.

Re: data step merge issue

Tom — Fri, 29 Apr 2022 14:29:48 GMT

All variables that are sourced from input dataset are "retained".

That is what allows you attach the FLAG value from the one observation in B to ALL of the observations in A for that BY group.

Once you change the value of FLAG it keeps that value until it is changed.

SAS will not read the next observation from B until the BY group values change.

To see if better use IF PARAM='bbb' instead of 'aaa'.

Just use a NEW variable for your desired result.

data c;
  merge a b;
  by subjid visit;
  if param ne 'aaa' then new_flag=flag;
run;

Result

Obs    subjid     visit    param    flag    new_flag

 1     101-001    vis01     aaa      Y
 2     101-001    vis01     bbb      Y         Y
 3     101-001    vis01     ccc      Y         Y
 4     101-001    vis02     aaa      Y
 5     101-001    vis02     bbb      Y         Y
 6     101-001    vis02     ccc      Y         Y

Re: data step merge issue

mkeintz — Fri, 29 Apr 2022 14:33:42 GMT

Your code could work as is - only if the param "aaa" is the last value in each id. I.e. you could replace the

proc sort data=a; by subjid visit; run;

with

proc sort data=a; by subjid visit descending param; run;

Not an appealing solution. You'll be better off with something like @Tom 's suggestion.

Re: data step merge issue

Sajid01 — Fri, 29 Apr 2022 15:45:21 GMT

Hello @Niugg2010
A simple way is to use Proc SQL. It works without any issue.

data a;
subjid="101-001";
do visit='vis01', 'vis02';
do param="aaa", 'bbb', 'ccc';
output;
end;
end;
run;
data b;
subjid="101-001";
do visit='vis01', 'vis02';
flag="Y";
output;
end;
run;
proc sql;
create table c as 
select a.*,
case 
when a.param="aaa" then ' '
else b.flag
end as Flag
from a left join b 
on a.subjid=b.subjid
and a.visit=b.visit;
quit;

The output would be

Re: data step merge issue

Niugg2010 — Mon, 02 May 2022 12:49:48 GMT

I paid less attention to this point in merge in my previous coding. Thanks everyone for help.