topic Creating monthly variables based on year of data for each ID level in SAS Programming

Creating monthly variables based on year of data for each ID level

Gumtm — Thu, 19 Jan 2012 23:24:10 GMT

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 .

Creating monthly variables based on year of data for each ID level

art297 — Thu, 19 Jan 2012 23:31:31 GMT

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.

Creating monthly variables based on year of data for each ID level

Gumtm — Fri, 20 Jan 2012 00:18:54 GMT

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!

Re: Creating monthly variables based on year of data for each ID level

Linlin — Fri, 20 Jan 2012 00:25:11 GMT

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;

total+1;

run;

Linlin

Creating monthly variables based on year of data for each ID level

art297 — Fri, 20 Jan 2012 00:26:09 GMT

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.

Re: Creating monthly variables based on year of data for each ID level

Ksharp — Fri, 20 Jan 2012 06:24:00 GMT

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