Assign one value to duplicate variables across rows

[hameliz]

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!!

[Tom · Accepted solution]

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.

[hameliz]

Thank you!  Where would I put 'Retain'?

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

thanks!

[Tom]

 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

[Tom]

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. 

[hameliz]

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 🙂