BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

11 REPLIES 11
shahparth260
Quartz | Level 8

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
Astounding
PROC Star

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;

righcoastmike
Quartz | Level 8

Hi Astounding, 

 

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

 

Mike

righcoastmike
Quartz | Level 8

That did it. Thanks so much! 

 

Mike 

PaigeMiller
Diamond | Level 26

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
righcoastmike
Quartz | Level 8
Hi Paige, 

 

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

 

Mike

righcoastmike
Quartz | Level 8

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

 

Mike

novinosrin
Tourmaline | Level 20
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;
righcoastmike
Quartz | Level 8

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 

 

novinosrin
Tourmaline | Level 20

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

righcoastmike
Quartz | Level 8

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 

 

 

 

 

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
  • 11 replies
  • 1463 views
  • 0 likes
  • 5 in conversation