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
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;
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.
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;
Hi Astounding,
Sorry for the confusion, The "month" variable is a SAS date variable, formatted in yymmdd10.
Mike
That did it. Thanks so much!
Mike
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);
The "month" variable is a SAS date variable, formatted in yymmdd10.
Mike
A quick point of Clarification - The "month" variable is a SAS date variable, formatted in yymmdd10.
Mike
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;
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
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???
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.