topic Re: Replacing IDs with the same value :Part2 in SAS Programming

Replacing IDs with the same value :Part2

Anita_n — Mon, 02 Aug 2021 10:05:03 GMT

Dear all,

I did ask this question previously about how ids with the same values the same subjid are assigned :

data have;
input id 2. subjid $6. var1 $2. var2 $2. ;
datalines;
1  pat_1 A B
14 pat_2 C D
14 pat_3 E F
14 pat_4 G H
2  pat_5 I J
;
run;

and got this reply as solution:

data want;
   set have(rename= (subjid = _subjid));
   by id notsorted;
   
   length subjid $ 6;
   retain subjid;
   
   if first.id then subjid = _subjid;
   
   drop _subjid;
run;

If I want the first id 14 to have a subjid pat_2_A

the second id 14 to have a subjid pat_2_B

and the third id 14 to have a subjid pat_2_C

instead of just pat_2, how to I do that?

Re: Replacing IDs with the same value :Part2

andreas_lds — Mon, 02 Aug 2021 10:24:33 GMT

Not clear, what you expect as value for subjid, if there is only one observation for the id. Please explain.

Re: Replacing IDs with the same value :Part2

Anita_n — Mon, 02 Aug 2021 11:05:30 GMT

Am expecting something like this:

id var1 var2 subjid
1 A B pat_1
14 C D pat_2_A
14 E F pat_2_B
14 G H pat_2_C
2 I J pat_5

Re: Replacing IDs with the same value :Part2

andreas_lds — Mon, 02 Aug 2021 11:15:52 GMT

have a look at

data want;
   set have(rename= (subjid = _subjid));
   by id notsorted;
   
   length 
      subjid $ 8 
      o_subjid $ 6
      count 8
   ;
   
   retain o_subjid count;
   
   if first.id then do;
      o_subjid = _subjid;
      count = 0;
   end;

   if not (first.id and last.id) then do;
      subjid = catx('_', o_subjid, byte(65 + count));
      count + 1;
   end;
   else do;
      subjid = o_subjid;
   end;
   
   drop o_subjid _subjid count;
run;

Re: Replacing IDs with the same value :Part2

Anita_n — Mon, 02 Aug 2021 11:46:42 GMT

perfect, thank you.