DATA Step, Macro, Functions and more

Conditional Retain on Multiple Variables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 183
Accepted Solution

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
Trusted Advisor
Posts: 1,554

Re: Conditional Retain on Multiple Variables

Posted in reply to Astounding

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,554

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: 5,498

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
Trusted Advisor
Posts: 1,554

Re: Conditional Retain on Multiple Variables

Posted in reply to Astounding

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;

Respected Advisor
Posts: 4,919

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 334 views
  • 2 likes
  • 4 in conversation