BookmarkSubscribeRSS Feed
Bailey
Calcite | Level 5

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;

4 REPLIES 4
Tom
Super User Tom
Super User

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;

user24feb
Barite | Level 11

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 496 views
  • 1 like
  • 5 in conversation