DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 2

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

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

IDYearMonthVarVar1_1
ID-120001XXXX
ID-120004XX0
ID-120026XX0
ID-219901XXXX
ID-220001XX0
ID-319992XX0


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 Smiley Sad.

PROC Star
Posts: 7,363

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

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.

New Contributor
Posts: 2

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

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!

Super Contributor
Posts: 1,636

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

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

PROC Star
Posts: 7,363

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

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.

Super User
Posts: 9,687

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

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

Ask a Question
Discussion stats
  • 5 replies
  • 215 views
  • 0 likes
  • 4 in conversation