topic Re: Conditional Retain on Multiple Variables in SAS Programming

Conditional Retain on Multiple Variables

Ujjawal — Thu, 01 Dec 2016 17:04:35 GMT

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

Re: Conditional Retain on Multiple Variables

Shmuel — Thu, 01 Dec 2016 17:50:29 GMT

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;

Re: Conditional Retain on Multiple Variables

Astounding — Thu, 01 Dec 2016 18:04:41 GMT

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.

Re: Conditional Retain on Multiple Variables

Shmuel — Thu, 01 Dec 2016 18:13:55 GMT

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;

Re: Conditional Retain on Multiple Variables

PGStats — Thu, 01 Dec 2016 18:25:50 GMT

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;