topic Convert long data to wide in SAS Programming

Convert long data to wide

angelicamagpan0 — Sat, 01 Feb 2020 23:08:12 GMT

I originally have that and i wanted to change the format so it would look like this for example:

Observation XML ZIP

1 35 10

2 97 44

3 100 190

I have tried this code:

proc transpose
data = my_data
out = diff_columns_data;
by exec_type;
var input_type;
run;

But my result looks like this

Any help would be greatly appreciated! Thanks!

Re: Convert long data to wide

novinosrin — Sat, 01 Feb 2020 23:42:25 GMT

data have;
input type $	exec_time;
cards;
xml	35
xml	97
xml	110
xml	250
xml	190
xml	110
xml	600
zip	10
zip	44
zip	65
zip	77
zip	43
zip	44
;

data temp;
 set have;
 by type;
 if first.type then n=0;
  n+1;
run;

proc sort data=temp;
by n;
run;

proc transpose data=temp out=want;
by n;
var exec_time;
id type;
run;

Re: Convert long data to wide

Kurt_Bremser — Sun, 02 Feb 2020 07:32:07 GMT

data want;
merge
  have (
    rename=(exec_time=xml)
    where=(input_type = "xml")
  )
  have (
    rename=(exec_time=zip)
    where=(input_type = "zip")
  )
;
run;

Re: Convert long data to wide

unison — Mon, 03 Feb 2020 03:58:44 GMT

Try double-transpose: (1) transpose by type, (2) by _name_:

data have;
input type $ exec_time;
cards;
xml 35
xml 97
xml 110
xml 250
xml 190
xml 110
xml 600
zip 10
zip 44
zip 65
zip 77
zip 43
zip 44
;
run;

proc transpose data=have out=have_trx;
by type;
var exec_time;
run;

proc transpose data=have_trx out=want(drop=_name_);
by _name_;
id type;
var col:;
run;