I need to create a new flag with subjects which has first id as "Y" and atleast 1 flag later for same id as "Y"
In the below example i want to see if ecs=1 has flag1="Y" then create flag2="Y" for that id
data WORK.ADS;
infile datalines dsd truncover;
input ID:BEST12. ecs:BEST12. par:$8. flag1:$1.;
datalines4;
12,1,par1,Y
12,2,par1,Y
12,3,par1,Y
12,4,par2,Y
13,1,par1,
13,2,par1,Y
13,3,par1,N
13,4,par2,Y
14,1,par1,N
;;;;
the output i need to get is falg2="Y" for id 12,14 and flag2="N" for 13
any help
Why would flag2="Y" for ID=14? It does not have Flag1="Y" for the first obs in the ID? Or later for that matter
orry, it should not be Y. my mistake
No problem. Given you have your ecs variable as in your sample data, you can do this
data want;
if _N_=1 then do;
declare hash h1(dataset:"ads(where=(ecs=1 & flag1='Y'))");
h1.definekey('ID');
h1.definedone();
declare hash h2(dataset:"ads(where=(ecs ne 1 & flag1='Y'))");
h2.definekey('ID');
h2.definedone();
end;
set ads;
if h1.check()=0 & h2.check()=0 then flag2='Y';
else flag2='N';
run;
Here is a non hash approach that does not depend on the ecs variable, but requires that your data is sorted by ID
data want;
do until (last.ID);
set ads;
by ID;
if first.ID then _iorc_=ifn(flag1='Y', 1, 0);
if first.ID ne 1 & _iorc_=1 & flag1='Y' then flag2='Y';
else flag2='N';
end;
do until (last.ID);
set ads;
by ID;
output;
end;
run;
Result:
ID ecs par flag1 flag2 12 1 par1 Y Y 12 2 par1 Y Y 12 3 par1 Y Y 12 4 par2 Y Y 13 1 par1 N 13 2 par1 Y N 13 3 par1 N N 13 4 par2 Y N 14 1 par1 N N
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.