transpose data without proc transpose

[rvsidhu035]

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

[PeterClemmensen]

There is A Better Way to Flip than PROC TRANSPOSE 🙂

[art297]

@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

[Kurt_Bremser]

---

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

[RW9 · Accepted solution]

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;
  

[Ksharp]

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;

[rvsidhu035]

call excute?