Conditional Retain on Multiple Variables

[Ujjawal]

I have dataset in the following format. The first 3 variables are already there in the dataset. I want to create the fourth variable 'Status'. By each ID and Code, I look at the variable 'flag' and want to generate serial numbers in the variable 'status'. If flag = 0, it should be prefixed with P otherwise Q. Please make sure serial number increment by 1 (See the row3 and row7 in the table below).

ID	flag	Code	Status
1	0	B1	P1
1	0	B2	P2
1	0	B2	P2
1	1	A1	Q1
1	1	A2	Q2
1	1	A3	Q3
2	0	D1	P3
2	0	D1	P3
2	1	C1	Q4
2	1	C1	Q4

[Shmuel]

Try next code:

data want;

  set have;

   by ID Flag;

         retain counter1 counter2 0;

        if first.flag then do;

           if flag=0 then counter1 +1;

                          else counter2 +1;

        end;

       else   if flag=1 then counter2 +1;

       if flag = 0 then status = 'P' || left(counter1);

                       else status = 'Q' || left(counter2);

run;

[Astounding]

That's a good idea to set up two counters.  I think you need to tweak the program to include CODE:

by ID Flag Code;

Then later:

if first.code then do;

In that case, the second ELSE statement could be removed.

[Shmuel · Accepted solution]

Thank you @Astounding, you are right - I missed the impact of CODE.

the code should be:

data want;

  set have;

   by ID Flag code;

         retain counter1 counter2 0;

        if first.code then do;

           if flag=0 then counter1 +1;

                          else counter2 +1;

        end;

       if flag = 0 then status = 'P' || left(counter1);

                       else status = 'Q' || left(counter2);

run;

[PGStats]

Use BY Code notsorted; magic:

data have;
input ID flag Code $ desiredStatus $;
datalines;
1	0	B1	P1
1	0	B2	P2
1	0	B2	P2
1	1	A1	Q1
1	1	A2	Q2
1	1	A3	Q3
2	0	D1	P3
2	0	D1	P3
2	1	C1	Q4
2	1	C1	Q4
;

data want;
set have; by code notsorted;
if flag then do;
    Q + first.code;
    Status = cats("Q", Q);
    end;
else do;
    P + first.code;
    Status = cats("P", P);
    end;
drop P Q;
run;

proc print data=want noobs; run;