DATA Step, Macro, Functions and more

maintaining same variable value across observations

Reply
Frequent Contributor
Posts: 110

maintaining same variable value across observations

Hi SAS community,

Per subject ID, I have multiple observations pending how many years the subject was involved in the project. As one would expect, the age of the subject ID increases by one with each year. However, I would like to create a line of code that takes forces the age variable value to stay the same across all years as it was in the subject's first year in the program. 

For example, see my dataset below.  The "old_age" variable is the subject's real age for each year.  The "new_age" is the age value I would like assigned to the subjectID.  How do I create this "new_age" variable?

SubjectID     year   old_age new_age

AAAAAA      2000     10          10

AAAAAA      2001     11          10

AAAAAA      2003     12          10

AAAAAA      2004     13          10

BBBBBB      2003     43          43

BBBBBB      2004     44          43

CCCCCC      2001     20          20

CCCCCC      2003     22          20

CCCCCC      2004     23          20

CCCCCC      2006     25          20

Thanks!

Frequent Contributor
Posts: 83

Re: maintaining same variable value across observations

Posted in reply to sophia_SAS

I like to use SQL to take minimums, but other people could easily give you data step commands to do both. But once you have the minimum specified from sql, just tell it to replace the age variable in data step. Hope this helps.

proc sql;

create table almost as

select distinct subjectid, year, age, (min(age)) as f_age

from have

group by subjectid;

quit;

data want;

     set almost;

     age = f_age;

     drop f_age;

run;

Regular Contributor
Posts: 180

Re: maintaining same variable value across observations

Posted in reply to sophia_SAS

The following code generates the table you want:

data have;

input subjectID $ year old_age;

cards;

AAAAAA      2000     10   

AAAAAA      2001     11   

AAAAAA      2003     12   

AAAAAA      2004     13   

BBBBBB      2003     43   

BBBBBB      2004     44   

CCCCCC      2001     20   

CCCCCC      2003     22   

CCCCCC      2004     23   

CCCCCC      2006     25   

;;

run;

proc sql;

  create table want as

  select subjectid, year, old_age, min(old_age) as new_age

  from have

  group by subjectid;

quit;

Buy I think what you really need is a table with only one row by subjectid:

proc sql;

  create table want as

  select distinct subjectid, min(year) as year, min(old_age) as new_age

  from have

  group by subjectid;

quit;

Regards,

Super User
Posts: 5,518

Re: maintaining same variable value across observations

Posted in reply to sophia_SAS

If your data are properly sorted, a DATA step can do this easily:

data want;

   set have;

   by subjectid;

   if first.subjectid then new_age = old_age;

   retain new_age;

run;

Good luck.

Super Contributor
Posts: 276

Re: maintaining same variable value across observations

Posted in reply to sophia_SAS

Hi,

You can achieve this easily using Retain statement and First . variables.

Using your Data,,

Data Test ;

input Sub_id $ year age  ;

cards;

AAAAAA 2000 10

AAAAAA 2001 11

AAAAAA 2003 12

AAAAAA 2004 13

BBBBBB 2003 43

BBBBBB 2004 44

CCCCCC 2001 20

CCCCCC 2003 22

CCCCCC 2004 23

CCCCCC 2006 25

;

run;

Data test1;

set Test;

retain new;

by sub_id;

if first.sub_id then new=age;

run;

Proc print;

run;

Regards,

Sanjeev.K

Ask a Question
Discussion stats
  • 4 replies
  • 446 views
  • 0 likes
  • 5 in conversation