topic Assign one value to duplicate variables across rows in SAS Procedures

Assign one value to duplicate variables across rows

hameliz — Thu, 28 Dec 2023 04:55:47 GMT

Hello!

I am trying to assign a value to duplicate rows.

This is the data I have:

Medrec Results
771441 Growth
771441 Growth
771441 No Growth
771441 No Growth
771441 No Growth

This is the data I want:

Medrec Results Growth
771441 Growth Y
771441 Growth Y
771441 No Growth Y
771441 No Growth Y
771441 No Growth Y

This is what I've tried but I only get one row to have Growth = 'Y'

proc sort data = medrec2; by medrec results;run;

data medrec3;

set medrec2;

by medrec;

if first.Medrec then do;

if results = 'Growth' then growth = 'y';

else if results = 'No Growth' then growth = 'n';

end;

run;

Thank you!!

Re: Assign one value to duplicate variables across rows

Tom — Thu, 28 Dec 2023 05:00:37 GMT

You forgot the RETAIN statement so that the value of GROWTH does not get set to missing at the start of each iteration of the data step.

Also what value to you want it to have if RESULTS is neither Growth nor No Growth? If you don't have another ELSE clause then any MEDREC group where the first value is neither of those two very specific values will retain the value calculated for the previous group.

Re: Assign one value to duplicate variables across rows

hameliz — Thu, 28 Dec 2023 05:00:47 GMT

Thank you! Where would I put 'Retain'?

And there are only two options = 'Growth' or 'No Growth'.

thanks!

Re: Assign one value to duplicate variables across rows

hameliz — Thu, 28 Dec 2023 05:05:53 GMT

I just did some searching and added it like this

proc sort data = medrec2; by medrec results;run;

data medrec3;

set medrec2;

by medrec;

if first.Medrec then do;

retain growth;

if results = 'Growth' then growth = 'y';

else if results = 'No Growth' then growth = 'n';

end;

run;

and it worked!!!

thank you 🙂

Re: Assign one value to duplicate variables across rows

Tom — Thu, 28 Dec 2023 05:07:14 GMT

It just tells SAS not to reset the new variable to missing at the start of the next iteration. So since it has no executable component you can place the retain statement any where in the data step.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/p0t2ac0tfzcgbjn112mu96hkgg9o.htm

Re: Assign one value to duplicate variables across rows

Tom — Thu, 28 Dec 2023 05:17:43 GMT

If you only have two possible values then why not just create a binary variable. That is easier to create

data medrec3;
  set medrec2;
  by medrec;
  if first.Medrec then growth= (results = 'Growth');
  retain growth;
run;

and easier to work with.