01-19-2012 06:24 PM
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
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 .
01-19-2012 06:31 PM
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.
01-19-2012 07:18 PM
Sorry as you can see I'm a SAS noob.
So I guess the data I already have is like:
input id $ year month varA;
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
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
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
Sorry if I'm still not being clear but thanks for responding!
01-19-2012 07:25 PM
Do you want something like this?
input id $ year month var;
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
data want(drop=total i j);
array c(3,12) var1_1-var1_12 var2_1-var2_12 var3_1-var3_12;
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;
01-19-2012 07:26 PM
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.
01-20-2012 01:24 AM
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;