BookmarkSubscribeRSS Feed
sophia_SAS
Obsidian | Level 7

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!

4 REPLIES 4
overmar
Obsidian | Level 7

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;

CTorres
Quartz | Level 8

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,

Astounding
PROC Star

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.

kuridisanjeev
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4621 views
  • 0 likes
  • 5 in conversation