## Conditional Retain on Multiple Variables

Solved
Regular Contributor
Posts: 185

# Conditional Retain on Multiple Variables

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

Accepted Solutions
Solution
‎12-01-2016 01:45 PM
Posts: 1,848

## Re: Conditional Retain on Multiple Variables

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;

All Replies
Posts: 1,848

## Re: Conditional Retain on Multiple Variables

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;

Super User
Posts: 6,935

## Re: Conditional Retain on Multiple Variables

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.

Solution
‎12-01-2016 01:45 PM
Posts: 1,848

## Re: Conditional Retain on Multiple Variables

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;

Posts: 5,626

## Re: Conditional Retain on Multiple Variables

[ Edited ]

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;

``````
PG
☑ This topic is solved.