BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ujjawal
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

4 REPLIES 4
Shmuel
Garnet | Level 18

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
PROC Star

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
Garnet | Level 18

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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