Help using Base SAS procedures

proc transpose repeatly?

Accepted Solution Solved
Reply
Super Contributor
Posts: 297
Accepted Solution

proc transpose repeatly?

[ Edited ]

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;


Accepted Solutions
Solution
‎06-16-2017 12:55 PM
Contributor
Posts: 22

Re: proc transpose repeatly?

[ Edited ]

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);

View solution in original post


All Replies
Trusted Advisor
Posts: 1,128

Re: proc transpose repeatly?

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;
Thanks,
Jag
Super Contributor
Posts: 297

Re: proc transpose repeatly?

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

Trusted Advisor
Posts: 1,128

Re: proc transpose repeatly?

may i know which are numeric and which are character
Thanks,
Jag
Solution
‎06-16-2017 12:55 PM
Contributor
Posts: 22

Re: proc transpose repeatly?

[ Edited ]

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);
Super Contributor
Posts: 297

Re: proc transpose repeatly?

That is awesome.  Thanks.

Super User
Posts: 10,486

Re: proc transpose repeatly?

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.

Super Contributor
Posts: 297

Re: proc transpose repeatly?

[ Edited ]

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.

Super User
Super User
Posts: 6,499

Re: proc transpose repeatly?

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;

Capture.PNG

Super Contributor
Posts: 297

Re: proc transpose repeatly?

Thank you so much. Great idea, Tom.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 247 views
  • 4 likes
  • 5 in conversation