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;
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;
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;
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.