turning wide data to long for many variables, extract suffix

[axescot78]

I'm trying to turn wide data into long and all variables are suffixed with year. There are many variables (500+) so I am trying to write a macro to do this. The first 3 variables vary slightly (are not present for all years) so I did them individually, merge them, and then create an array of variable names which goes into a do loop to run the macro. The macro uses proc transpose and merges to the dataset that is already started. However, I am getting an error which I have narrowed down to the do loop. Any advice?

Data that I have:

data have;
input studyid group var1_01 var1_04 var1_07 var2_01 var2_04 var2_07;
datalines;
2218 3 1 0 1 1 1 0
3779 1 1 0 1 1 0 1
5914 1 1 0 1 1 0 1
;

Data want:

data want;
input studyid group year var1 var2;
datalines;
2218 3 _01 1 1
2218 3 _04 0 1
2218 3 _07 1 0
3779 1 _01 1 1
3779 1 _04 0 0
3779 1 _07 1 1
5914 1 _01 1 1
5914 1 _04 0 0
5914 1 _07 1 1
;

Macros:

%LET Y1 = '_01';
%LET Y2 = '_04';
%LET Y3 = '_07';

%MACRO reshape(col);
	proc transpose data=subset_cols 
			out=long_data 
			prefix=&col ;
		by studyid;
		var &col.&Y1 &col.&Y2 &col.&Y3 &co1.&Y4 &col.&Y5 ;

	data combined;
		merge combined 
			long_data (rename=(&col.1=&col)) ;
		by studyid;
		year = substr(_name_, length(_name_)-2, 3);
		drop _name_;

%MEND reshape;

Do loop to call on macro:

data long_data_4;
	set wide_data;
	array col_names (*) 
		var4
		var5
		;

	do i=1 to dim(col_names);
		%reshape(col_names(i));
	end;

run;

Error that I am getting:

[PeterClemmensen]

Do all the variables begin with either var1 or var2?

[axescot78]

No, they each have their own names. But I narrowed this down to the do loop. I am updating my post now.

[PeterClemmensen]

See if you can use this as a template

data have;
input studyid group var1_01 var1_04 var1_07 var2_01 var2_04 var2_07;
datalines;
2218 3 1 0 1 1 1 0
3779 1 1 0 1 1 0 1
5914 1 1 0 1 1 0 1
;

data want;
   set have;
   array _1 {*} var1:;
   array _2 {*} var2:;

   do i = 1 to dim(_1);
      year = scan(vname(_1[i]), -1, '_');
      var1 = _1[i];
      var2 = _2[i];
	  output;
   end;

   keep studyid group year var1 var2;
run;

[axescot78]

It's the way I am calling the macro because it works outside of the do loop

[axescot78 · Accepted solution]

So I took it out of the do loop and ran each variable in the macro independently using ctrl+h

[Kurt_Bremser]

Without any macro coding, using two TRANSPOSEs:

proc transpose data=have out=long1;
by studyid group;
var var:;
run;

data long2;
set long1;
year = scan(_name_,2,"_");
_name_ = scan(_name_,1,"_");
run;

proc sort data=long2;
by studyid group year;
run;

proc transpose data=long2 out=want (drop=_name_);
by studyid group year;
var col1;
id _name_;
run;

The only thing you need to do is expanding the VAR statement in the first TRANSPOSE.

[s_lassen]

The error you get is because you are trying to run a macro that runs a PROC TRANSPOSE and a datastep inside a datastep DO loop. If you turn on option MPRINT, you will see what happens.

I assume that you want to run this type of transformation for many more variables. If they all have the same year suffixes, you can do something like this (based on the solution by @PeterClemmensen )

data have;
input studyid group var1_01 var1_04 var1_07 var2_01 var2_04 var2_07;
datalines;
2218 3 1 0 1 1 1 0
3779 1 1 0 1 1 0 1
5914 1 1 0 1 1 0 1
;run;

%macro transpose_vars(vars);             
  %local i w;                            
  %do i=1 %to %sysfunc(countw(&vars));   
    %let w=%scan(&vars,&i);              
    array _&i (*) &w:;                   
    %end;                                
  do _N_=1 to dim(_1);               
    length year $2;    
    year = scan(vname(_1(_N_)),-1,'_');  
    %do i=1 %to %sysfunc(countw(&vars)); 
      %let w=%scan(&vars,&i);            
      &w=_&i (_N_);                      
      %end;                              
    output;                              
    end;                                 
  keep studyid group year &vars;         
%mend;                                   
options mprint;                          
data want;                               
   set have;                             
   %transpose_vars(var1 var2);           
run;

[PeterClemmensen]

@s_lassen cool 🙂