DATA Step, Macro, Functions and more

Trying to create a dynamic age variable

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Trying to create a dynamic age variable

[ Edited ]

Hi All, here's the issue: 

 

I have a panel data set with 1 record per person in my study for each month of the study (study is 5 years so 60 records per individual) It looks something like this: ***EDIT**** The month variable is formatted as a SAS date yymmdd10

 

data have;
input patientID Age month;
cards;
1 18 04-2015
1 18 05-2015
1 18 06-2015
1 18 07-2015
1 18 08-2015
1 18 09-2015
1 18 10-2015
1 18 11-2015
1 18 12-2015
;

 The challenge that I am running into is that the "age" variable I was given is calculated as the persons age at the beginning of the study, and it is static (so if someone is 18 at the beginning of the study, the the value of "age" is 18 in all 60 records, even though they clearly aren't 18 after the first year.) 

 

 I'm hoping to generate a new variable that will update the given "age variable" every month.  Because I don't actually know the birthdays of my participants, for the purposes of creating this variable, everyone's birthday is July 2nd. So on July 2nd everyone gets a year older. 

 

so Ideally my new dataset would look like:

 

data have;
input patientID Age month;
cards;
1 18 04-2015
1 18 05-2015
1 18 06-2015
1 18 07-2015
1 19 08-2015
1 19 09-2015
1 19 10-2015
1 19 11-2015
1 19 12-2015
;

If I was just doing it for one person over one year it would be simple, but since I am dealing with thousands of people over 5 years I'm finding it a little tricky. 

 

As always, any help is much appreciated. 

 

Thanks so much

 

Mike 

 

 


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 6,638

Re: Trying to create a dynamic age variable

[ Edited ]
Posted in reply to righcoastmike

Assuming MONTH is a character string, as those are not valid numbers in SAS ....

 

Assuming your data is sorted as indicated ....

 

data want;

set have;

by patientID;

if first.patientID then newage = age;

else if month =: '08' then newage + 1;

retain newage;

run;

 

 

************** EDITED for the case when MONTH is actually a SAS date:

 

else if month(month)=8 then newage + 1;

View solution in original post


All Replies
Occasional Contributor
Posts: 15

Re: Trying to create a dynamic age variable

Posted in reply to righcoastmike

I think you should do grouping and based on that if first./ if  last.  and retain  on group would work. 
I am trying from my side but let's see.

 

PS
Solution
2 weeks ago
Super User
Posts: 6,638

Re: Trying to create a dynamic age variable

[ Edited ]
Posted in reply to righcoastmike

Assuming MONTH is a character string, as those are not valid numbers in SAS ....

 

Assuming your data is sorted as indicated ....

 

data want;

set have;

by patientID;

if first.patientID then newage = age;

else if month =: '08' then newage + 1;

retain newage;

run;

 

 

************** EDITED for the case when MONTH is actually a SAS date:

 

else if month(month)=8 then newage + 1;

Contributor
Posts: 34

Re: Trying to create a dynamic age variable

Posted in reply to Astounding

Hi Astounding, 

 

Sorry for the confusion,  The "month" variable is a SAS date variable, formatted in yymmdd10.

 

Mike

Contributor
Posts: 34

Re: Trying to create a dynamic age variable

Posted in reply to Astounding

That did it. Thanks so much! 

 

Mike 

Respected Advisor
Posts: 2,833

Re: Trying to create a dynamic age variable

Posted in reply to righcoastmike

Well assuming the month shown (e.g. 04-2015) is an actual SAS date formatted somehow, and not the character string '04-2015'. If it's not a SAS date, then you have to convert it to an actual SAS date.

 

assumed_birthday=mdy(7,2,2015-age);

assumed_age=intck('year',assumed_birthday,month);

--
Paige Miller
Contributor
Posts: 34

Re: Trying to create a dynamic age variable

Posted in reply to PaigeMiller
Hi Paige, 

 

The "month" variable is a SAS date variable, formatted in yymmdd10.

 

Mike

Contributor
Posts: 34

Re: Trying to create a dynamic age variable

Posted in reply to righcoastmike

A quick point of Clarification - The "month" variable is a SAS date variable, formatted in yymmdd10.

 

Mike

PROC Star
Posts: 1,593

Re: Trying to create a dynamic age variable

Posted in reply to righcoastmike
data have;
input patientID Age month :anydtdte21.;
format month yymmdd10.;
cards;
1 18 04-2014
1 18 05-2014
1 18 06-2014
1 18 07-2014
1 18 08-2015
1 18 09-2015
1 18 10-2015
1 18 11-2015
1 18 12-2015
;

data want;
set have;
by patientID;
retain new_age;
if first.patientID then new_age=age;
else if year(month)-year(lag(month))=1 then new_age=age+1;
run;
Contributor
Posts: 34

Re: Trying to create a dynamic age variable

Posted in reply to novinosrin

Thanks Novino, This one almost worked but only ever advanced the year by 1 (so it worked for the first year, but didn't for years 2-5). Astounding ended up cracking it by "retaining" the new variable. 

 

Thanks again for all your help, Astounding's solution wouldn't have worked if you hadn't helped me get the data into proper date format beforehand. 

 

Mike 

 

PROC Star
Posts: 1,593

Re: Trying to create a dynamic age variable

Posted in reply to righcoastmike

I am glad. Just wanna know what you mean by

 but didn't for years 2-5)

 

do you mean 2 years of difference in years or more in your sample???

Contributor
Posts: 34

Re: Trying to create a dynamic age variable

Posted in reply to novinosrin

Sorry, that was unclear, 

 

I think there were 2 issues with the code: 

1. It changed people ages based on year, not month. so people got older on Jan 1st not Aug 1st.  normally that would be great but because of the nature of the study, I need everyones birthdays to be on July 2nd (so the first age increase would show up on Aug 1st)

 

and

 

2. your code set the parameters for when 1 should be added to the original "age" so that worked perfectly for the first year. 

The trouble popped up when, in year 2 the computer correctly recognized that it needed to add 1, but it kept adding to the original age variable, not the new age variable. So the age of the person went up the first year, but then stayed the same for the rest of the years

 

For example, your code returned this:

 

First year of study age=18 

second year of study age=19 

third year of study age=19 

fourth year of study age=19 

 

instead of this

 

First year of study age=18 

second year of study age=19 

third year of study age=20

fourth year of study age=21 

 

It could very well have been an input error on my part (i'm using an air gapped machine so had to transfer everything over manually, no copy/paste) but those were the issues that I ran into. 

 

hope that clears things up, and thanks again. 

 

Mike 

 

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 139 views
  • 0 likes
  • 5 in conversation