BookmarkSubscribeRSS Feed
Gumtm
Calcite | Level 5

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.

5 REPLIES 5
art297
Opal | Level 21

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.

Gumtm
Calcite | Level 5

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!

Linlin
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

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.

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1265 views
  • 0 likes
  • 4 in conversation