topic Re: Need to make a flag for id which has first flag as Y in SAS Programming

Need to make a flag for id which has first flag as Y

noda6003 — Mon, 09 Sep 2019 10:19:16 GMT

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

Re: Need to make a flag for id which has first flag as Y

PeterClemmensen — Mon, 09 Sep 2019 10:21:33 GMT

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

Re: Need to make a flag for id which has first flag as Y

noda6003 — Mon, 09 Sep 2019 12:46:58 GMT

orry, it should not be Y. my mistake

Re: Need to make a flag for id which has first flag as Y

PeterClemmensen — Mon, 09 Sep 2019 12:59:23 GMT

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;

Re: Need to make a flag for id which has first flag as Y

PeterClemmensen — Tue, 10 Sep 2019 07:03:41 GMT

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