I need help creating variables based on the year and month for a set of longitudinal data where the first year of data for each person is '1' and monthly variables are created : Var1_1 - Var1_12
This needs to be done for all subsequent years until all the data for each person is exhausted.
And the observation falls in e.g. 1_1 then it equals the var data, but if there is no data for that year/month, then value equals zero.
Below is a sample of what my data looks like
ID | Year | Month | Var | Var1_1 |
---|---|---|---|---|
ID-1 | 2000 | 1 | XX | XX |
ID-1 | 2000 | 4 | XX | 0 |
ID-1 | 2002 | 6 | XX | 0 |
ID-2 | 1990 | 1 | XX | XX |
ID-2 | 2000 | 1 | XX | 0 |
ID-3 | 1999 | 2 | XX | 0 |
So for example the third line would only have corresponding data in Var3_6 -- since 2002 is the third year for ID-1
Any help would be greatly appreciated. I've been trying to do a loop for each person, but nothing is working out .
I, for one, don't understand what you are asking. You mention variables that don't appear in your snapshot.
It would be easier to understand if you showed your example data as a datastep, and also included a second datastep of what you want the example records to look like after processing.
Sorry as you can see I'm a SAS noob.
So I guess the data I already have is like:
data example;
input id $ year month varA;
cards;
ID-1 2000 1 XX
ID-1 2000 4 XX
ID-1 2002 6 XX
ID-2 1990 1 XX
ID-2 2000 1 XX
ID-3 1999 2 XX
run;
Now I want to create a series of variables var1_1 to var1_12, var2_1 to var2_12, and so on.
Below is what I want it to look like, but I've only included up to var1_3
data final;
input id $ year month var var1_1 var1_2 var1_3;
ID-1 2000 1 XX XX 0 0
ID-1 2000 4 XX 0 0 0
ID-1 2002 6 XX 0 0 0
ID-2 1990 1 XX XX 0 0
ID-2 2000 1 XX 0 0 0
ID-3 1999 2 XX 0 XX 0
run;
Sorry if I'm still not being clear but thanks for responding!
Do you want something like this?
data have;
input id $ year month var;
cards;
ID-1 2000 1 20
ID-1 2001 4 30
ID-1 2002 6 40
ID-2 1990 1 50
ID-2 2000 1 60
ID-3 1999 2 70
;
run;
data want(drop=total i j);
array c(3,12) var1_1-var1_12 var2_1-var2_12 var3_1-var3_12;
set have;
by id;
if first.id then total=1;
do i=1 to 3;
do j=1 to 12;
if (i=total and j=month) then c(i,j)=var;
else c(i,j)=0;
end;
end;
total+1;
run;
Linlin
No need to appologize regarding how much SAS you know (we all, at some point, were newbees with SAS). However, I still don't understand the relationships between your input and output data files and the specifications you mentioned.
How about:
data have; input id $ year month var; cards; ID-1 2000 1 20 ID-1 2000 4 30 ID-1 2002 6 40 ID-1 2004 6 40 ID-1 2006 6 40 ID-2 1990 1 50 ID-2 2000 1 60 ID-3 1999 2 70 ; run; data have; set have; flag+dif(year) ; if id ne lag(id) then flag=1; run; data _null_; set have end=last; if _n_ eq 1 then call execute('data want; '); call execute(cats('id="',id,'";','year=',year,';','month=',month,';','var=',var,';')); call execute(cats('var',flag,'_',month,'=',var,';output;call missing(of _all_);')); if last then call execute('run;'); run; proc sql noprint; select name into : list separated by ' ' from dictionary.columns where libname='WORK' and memname='WANT' and upcase(name) like 'VAR%~_%' escape '~' order by input(scan(name,1,' ','kd'),best8.),input(scan(name,2,' ','kd'),best8.) ; quit; %put &list; data want; retain id year month var &list; set want; run; proc stdize data=want out=want reponly missing=0; var &list; run;
Ksharp
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.