topic Re: proc transpose repeatly? in SAS Procedures

proc transpose repeatly?

ybz12003 — Tue, 20 Jun 2017 11:24:23 GMT

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;

Re: proc transpose repeatly?

Jagadishkatam — Fri, 16 Jun 2017 15:19:12 GMT

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;

Re: proc transpose repeatly?

ybz12003 — Fri, 16 Jun 2017 15:22:36 GMT

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

Re: proc transpose repeatly?

Jagadishkatam — Fri, 16 Jun 2017 15:26:00 GMT

may i know which are numeric and which are character

Re: proc transpose repeatly?

Rwon — Fri, 16 Jun 2017 15:28:16 GMT

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

Re: proc transpose repeatly?

ballardw — Fri, 16 Jun 2017 15:58:30 GMT

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.

Re: proc transpose repeatly?

ybz12003 — Fri, 16 Jun 2017 16:28:55 GMT

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.

Re: proc transpose repeatly?

Tom — Fri, 16 Jun 2017 16:51:31 GMT

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;

Re: proc transpose repeatly?

ybz12003 — Fri, 16 Jun 2017 16:54:46 GMT

That is awesome. Thanks.

Re: proc transpose repeatly?

ybz12003 — Fri, 16 Jun 2017 16:56:53 GMT

Thank you so much. Great idea, Tom.