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!
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;
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,
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.