proc transpose repeatly?

[ybz12003]

Hello:

I have 10 very similar proc transpose programs.  Is there a way to simply them?  Maybe a macro or do loop something?  The oringal data set is shown in the attachment

proc transpose data=test out=trans3

prefix=TAN;

var TAN_1-TAN_3;

by ID Name;

run;

proc transpose data=test out=trans4

prefix=CAN;

var CAN_1-CAN_5;

by ID Name;

run;

proc transpose data=test out=trans5

prefix=ROOD;

var ROOD_1-ROOD_8;

by ID Name;

run;

quit;

[Jagadishkatam]

you could try the arrays please find an untested code

, the below program i wrote where i considered the tab can and rood variables are numeric. So in want we get the newvar numeric variable. 

data want;
set have;
array vars(*) tan_1-tan_3 can_1-can_5 rood_1-rood_8;
do i = 1 to dim(vars);
if vars(i) ne . then newvar=vars(i);
output;
end;
run;

[ybz12003]

No, some of them are numeric and some of them are charater.

[Jagadishkatam]

may i know which are numeric and which are character

[Rwon · Accepted solution]

This will save some space. I use this structure when I have repeated procedures in a code.

%macro trans(i, pre, var_num);

  proc transpose data=test out=trans&i. prefix=&pre.;
    var &pre._1-&pre._&var_num.;
    by ID Name;
    run; quit;

%mend;

%trans(i=1, pre=TAN, var_num=3);
%trans(i=2, pre=CAN, var_num=5);
%trans(i=3, pre=ROOD, var_num=8);

[ybz12003]

That is awesome.  Thanks.

[ballardw]

Show the input and desired final output.

I have a suspicion you are combining the resulting sets and we really should see what that result should be.

[ybz12003]

I have attached the dataset on the first message.  You're right.  I did plan to combine all of dataset after transpose.  Is there going to be a problem when the dataset is not in the same format?  I plan to use proc sql.

[Tom]

As long as you do not care that your numeric variables are converted to character and back to numeric you can perhaps just process all of them with two proc transpose calls and a little more work in between.

First make a tall skinny table and then parse the names into prefix and numeric suffix.

proc transpose data=have out=middle ;
  by id name ;
  var tan: can: ;
run;

data fix_middle ;
  set middle ;
  row = input(scan(_name_,2,'_'),32.);
  _name_=scan(_name_,1,'_');
run;

Then resort to mark the rows match-up and transpose again.

proc sort;
  by id name row _name_;
run;

proc transpose data=fix_middle out=want ;
  by id name row ;
  id _name_;
  var col1 ;
run;

Now add a step to convert the numeric variables back.

data fix_want ;
  set want ;
  tan_num = input(tan,??32.);
  rename tan_num=tan ;
  drop _name_ tan ;
run;

[ybz12003]

Thank you so much. Great idea, Tom.