topic Re: transpose data without proc transpose in SAS Programming

transpose data without proc transpose

rvsidhu035 — Tue, 03 Apr 2018 08:31:50 GMT

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

Re: transpose data without proc transpose

PeterClemmensen — Tue, 03 Apr 2018 08:59:46 GMT

There is A Better Way to Flip than PROC TRANSPOSE 🙂

Re: transpose data without proc transpose

Kurt_Bremser — Tue, 03 Apr 2018 09:08:28 GMT

@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.

Re: transpose data without proc transpose

RW9 — Tue, 03 Apr 2018 09:11:07 GMT

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;

Re: transpose data without proc transpose

Ksharp — Tue, 03 Apr 2018 12:23:45 GMT

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;

Re: transpose data without proc transpose

rvsidhu035 — Tue, 03 Apr 2018 15:53:58 GMT

call excute?

Re: transpose data without proc transpose

art297 — Tue, 03 Apr 2018 18:12:41 GMT

@PeterClemmensen: @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