BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Rwon
Obsidian | Level 7

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

9 REPLIES 9
Jagadishkatam
Amethyst | Level 16

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
ybz12003
Rhodochrosite | Level 12

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

Jagadishkatam
Amethyst | Level 16
may i know which are numeric and which are character
Thanks,
Jag
Rwon
Obsidian | Level 7

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
Rhodochrosite | Level 12

That is awesome.  Thanks.

ballardw
Super User

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

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

ybz12003
Rhodochrosite | Level 12

Thank you so much. Great idea, Tom.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 1840 views
  • 4 likes
  • 5 in conversation