topic transposing a data set in SAS Programming

transposing a data set

Bailey — Thu, 31 Jul 2014 02:38:17 GMT

How do I transpose the following data set (WITHOUT using PROC TRANSPOSE) so that in the end, the output looks like this (in reality I have a LARGE data set, so I would really like an array statement somewhere!

Start End

DATA exam; 30 35

input id $ e1-e4; 15 16

DATALINES; 20 25

Start 30 15 20 45 45 42

End 35 16 25 42

;

RUN;

Re: transposing a data set

Tom — Thu, 31 Jul 2014 03:16:27 GMT

As long as each ID is unique.

data HAVE ;

input id e1-e4 ;

cards;

Start 30 15 20 45

End 35 16 25 42

run;

%let varlist=e1-e4 ;

%let idvar=id ;

proc sql noprint ;

select catq('n',&idvar)

into :names separated by ' '

from HAVE

;

quit;

%let nrow = &sqlobs;

data WANT ;

* Define variables from HAVE without reading any data yet;

if 0 then set HAVE;

* Define source array so that can use DIM() to loop ;

array source &varlist ;

* Loop once for each source column ;

do _n_=1 to dim(source);

* Loop over all rows of data ;

do p=1 to &nrow;

set HAVE point=p ;

array target (&nrow) 8 &names ;

target(p) = source(_n_) ;

end;

output;

end;

drop &idvar &varlist ;

stop;

run;

proc print data=HAVE;

run;

proc print data=WANT;

run;

Re: transposing a data set

user24feb — Thu, 31 Jul 2014 07:31:21 GMT

Just an idea:

DATA exam;
input id $ e1-e4;
DATALINES;
Start 30 15 20 45
End 35 16 25 42
;
RUN;

Proc Sort Data=exam;
By id;
Run;

Data Start (Keep=Value Rename=(Value=Start)) End (Keep=Value Rename=(Value=End));
Set exam;
Array N _NUMERIC_;
By ID;
Do over N;
Value=N;
ID=id;
If ID eq "Start" Then Output Start;
Else Output End;
End;
Run;

Data exam_Trans;
Merge Start End;
Run;

Proc Print Data=exam Noobs; Run;
Proc Print Data=exam_Trans Noobs; Run;

Re: transposing a data set

RW9 — Thu, 31 Jul 2014 08:35:37 GMT

Without merging:

data HAVE;
input id $ e1-e4;
cards;
Start 30 15 20 45
End 35 16 25 42
run;

data want (drop=e1-e4 j);
set have;
attrib start end format=best.;
array e{4};
array i{2} start end;
do j=1 to 4;
i{1}=lag(e{j});
i{2}=e{j};
if id="End" then output;
end;
run;

Re: transposing a data set

Ksharp — Thu, 31 Jul 2014 13:19:33 GMT

Assuming a row correspond to a unique id .


data HAVE ;
  input id $ e1-e4 ;
cards;
Start 30 15 20 45
End 35 16 25 42
s 30 15 20 45
n 35 16 25 42
;
run; 
proc sql;
create table id as
 select distinct id from have;
quit;

data temp(keep=id value);
 set have;
 array x{*} e1-e4;
 do i=1 to dim(x);
  value=x{i};output;
 end;
run;
data _null_;
 set id end=last;
 if _n_ eq 1 then call execute('data want(drop=id);merge ');
 call execute(cats('temp(where=(id="',id,'") rename=(value=',id,'))'));
 if last then call execute(';run;');
run;

Xia Keshan