DATA Step, Macro, Functions and more

transpose data without proc transpose

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

transpose data without proc transpose

data new30;
Input id $ sysbp diabp;
Cards;
001 130 80
002 110 90
003 140 70
004 120 80
005 130 70
;
Transpose the above dataset without using proc transpose.
_name_ 001 002 003 004 005
Sysbp 130 110 140 120 130
Diabp 80 90 70 80 70


Accepted Solutions
Solution
‎04-03-2018 05:33 AM
Super User
Super User
Posts: 9,840

Re: transpose data without proc transpose

[ Edited ]
Posted in reply to rvsidhu035

And why do you not want to use proc transpose - given that the proc transpose was built, tested, and specifically optimised for such a procedure this does not make any sense?

 

If you do not use the procedure designed for such a task then you need to have a few extra steps, and you need to make assumptions about your data.  

data new30;
  input id $ sysbp diabp;
cards;
001 130 80
002 110 90
003 140 70
004 120 80
005 130 70
;
run;

proc sql noprint;
  select distinct cats("_",id) into :tot separated by " " from new30;
quit;

data want1 (keep=_name_ _:);
  set new30 end=last;
  array dat &tot.;
  retain _:;
  _name_="sysbp";
  do i=1 to dim(dat);
    if cats("_",id)=vname(dat{i}) then dat{i}=sysbp;
  end;
  if last then output;
run;
  
data want2 (keep=_name_ _:);
  set new30 end=last;
  array dat &tot.;
  retain _:;
  _name_="diabp";
  do i=1 to dim(dat);
    if cats("_",id)=vname(dat{i}) then dat{i}=diabp;
  end;
  if last then output;
run;

data want;
  set want1 want2;
run;

And then of course once you have created the code, you can start to condense it:

proc sql noprint;
  select distinct cats("_",id) into :tot separated by " " from new30;
quit;

%macro tran (type=,id=);
  data want&id. (keep=_name_ _:);
    set new30 end=last;
    array dat &tot.;
    retain _:;
    _name_="&type.";
    do i=1 to dim(dat);
      if cats("_",id)=vname(dat{i}) then dat{i}=&type.;
    end;
    if last then output;
  run;
%mend tran;

%tran (type=sysbp,id=1);
%tran (type=diabp,id=2);

data want;
  set want1 want2;
run;
  

 

View solution in original post


All Replies
PROC Star
Posts: 1,402

Re: transpose data without proc transpose

Posted in reply to rvsidhu035

There is A Better Way to Flip than PROC TRANSPOSE Smiley Happy

Super User
Posts: 8,218

Re: transpose data without proc transpose

@draycut@rvsidhu035 already has accepted a solution but, of course, I agree with you. In this case I, personally, would use the macro after running proc transpose to make the dataset long. i.e.:

data new30;
  input id $ sysbp diabp;
cards;
001 130 80
002 110 90
003 140 70
004 120 80
005 130 70
;
run;

proc transpose data=new30 out=need;
  var sysbp diabp;
  by id;
run;

%transpose(data=need, out=want, by=_name_, id=id, 
 prefix=_, var=col1, use_varname=no, sort=yes)

Requires a lot less code, has a lot less chance of making an error, and should take a lot less time than all of those steps.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 10,574

Re: transpose data without proc transpose

[ Edited ]
Posted in reply to rvsidhu035

@rvsidhu035 wrote:


Transpose the above dataset without using proc transpose.


"Walk, but don't use your legs". Somebody mislaid their brain.

 

BTW 001 002 003 004 005 are not valid SAS names, so that's idiocy #2.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎04-03-2018 05:33 AM
Super User
Super User
Posts: 9,840

Re: transpose data without proc transpose

[ Edited ]
Posted in reply to rvsidhu035

And why do you not want to use proc transpose - given that the proc transpose was built, tested, and specifically optimised for such a procedure this does not make any sense?

 

If you do not use the procedure designed for such a task then you need to have a few extra steps, and you need to make assumptions about your data.  

data new30;
  input id $ sysbp diabp;
cards;
001 130 80
002 110 90
003 140 70
004 120 80
005 130 70
;
run;

proc sql noprint;
  select distinct cats("_",id) into :tot separated by " " from new30;
quit;

data want1 (keep=_name_ _:);
  set new30 end=last;
  array dat &tot.;
  retain _:;
  _name_="sysbp";
  do i=1 to dim(dat);
    if cats("_",id)=vname(dat{i}) then dat{i}=sysbp;
  end;
  if last then output;
run;
  
data want2 (keep=_name_ _:);
  set new30 end=last;
  array dat &tot.;
  retain _:;
  _name_="diabp";
  do i=1 to dim(dat);
    if cats("_",id)=vname(dat{i}) then dat{i}=diabp;
  end;
  if last then output;
run;

data want;
  set want1 want2;
run;

And then of course once you have created the code, you can start to condense it:

proc sql noprint;
  select distinct cats("_",id) into :tot separated by " " from new30;
quit;

%macro tran (type=,id=);
  data want&id. (keep=_name_ _:);
    set new30 end=last;
    array dat &tot.;
    retain _:;
    _name_="&type.";
    do i=1 to dim(dat);
      if cats("_",id)=vname(dat{i}) then dat{i}=&type.;
    end;
    if last then output;
  run;
%mend tran;

%tran (type=sysbp,id=1);
%tran (type=diabp,id=2);

data want;
  set want1 want2;
run;
  

 

Super User
Posts: 10,850

Re: transpose data without proc transpose

Posted in reply to rvsidhu035
data new30;
Input id $ sysbp diabp;
Cards;
001 130 80
002 110 90
003 140 70
004 120 80
005 130 70
;
data have;
 set new30;
 _name_='sysbp';value=sysbp;output;
 _name_='diabp';value=diabp;output;
 keep id _name_ value;
run;
proc freq data=new30 noprint;
table id/out=id nopercent;
run;
data _null_;
 set id end=last;
 if _n_=1 then call execute('data want;merge ');
 call execute(catt('have(where=(id="',id,'") rename=(value=_',id,'))'));
 if last then call execute(';drop id;run;');
run;
Contributor
Posts: 56

Re: transpose data without proc transpose

call excute?

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 305 views
  • 3 likes
  • 6 in conversation