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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.