transposing a data set

Reply
Occasional Contributor
Posts: 7

transposing a data set

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;

Super User
Super User
Posts: 6,317

Re: transposing a data set

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;

Super Contributor
Posts: 336

Re: transposing a data set

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;

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: transposing a data set

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;

Grand Advisor
Posts: 9,571

Re: transposing a data set

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

Ask a Question
Discussion stats
  • 4 replies
  • 217 views
  • 1 like
  • 5 in conversation