DATA Step, Macro, Functions and more

Transpose data using array or proc tranpose

Reply
Frequent Contributor
Posts: 78

Transpose data using array or proc tranpose

Can somebody give a thought how it will be easier to get below output...

I have input data as below :

--------------

Input Data
--------------
53809,09,01-SEP-2015,0:15,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
53809,09,01-SEP-2015,0:30,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
53809,09,01-SEP-2015,0:45,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,3    
53809,09,01-SEP-2015,1:00,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2

 

And I am looking if I could have output something like below:

 

---------------------

Output Desired:

---------------------


SUBSYS,Month,Date,Time,#Blades,1MA,1MB,1ME,1MF,2MC,2MD,2MC,2MH
53809,09,01-SEP-2015,0:15,8,1,1,2,1,1,1,1,2
53809,09,01-SEP-2015,0:30,8,1,1,2,1,1,1,1,2
53809,09,01-SEP-2015,0:45,8,1,1,3,1,1,1,1,3
53809,09,01-SEP-2015,1:00,8,1,1,3,1,1,1,1,2

 

So basically as you see the 1MA,1MB etc get the heading and then corresponding values get at the bottom of those 1MA,1MB ...

 

I see there's possibility to have it done using the Proc transpose or array can be done however don't seems to come out exactly...

Super User
Posts: 6,938

Re: Transpose data using array or proc tranpose

If the data in the records is always ordered in the same manner, you just read the file and drop the columns that contain the 1MA, 1MB and so on.

Have the remaining columns named appropriately and proc export will give you your desired output.

 

If the order is not consistent, you will need to read the data into columns that are grouped into arrays (one for names, one for values), so you can evaluate and assign the name/value pairs in a do loop.

 

So, which is it?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 78

Re: Transpose data using array or proc tranpose

I believe should have included one more line in the input which make it tuff not to use the hardcoded value..so as in below example i added one more row so with the change of susbsy variable(which is first 5 digit no.) it's no guarantee we will have same column values..

So example as below when 98909 came in the things changed ...

 

Input Data
-----------
53809,09,01-SEP-2015,0:15,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
53809,09,01-SEP-2015,0:30,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
53809,09,01-SEP-2015,0:45,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,3    
53809,09,01-SEP-2015,1:00,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
98909,09,01-SEP-2015,0:15,4,2MA,1,2MB,1,3ME,3,3MF,1,3MC,1,4MD,1,4MG,1,5MH,2

Super User
Posts: 6,938

Re: Transpose data using array or proc tranpose

So we need to go the hard way Smiley Wink

 

%let maxpair=8;

data have;
infile cards dlm=',' missover;
informat
  subsys 5.
  month 2.
  date date11.
  time time6.
;
format
  date date11.
  time time6.
;
length name1-name&maxpair $3;
array names {&maxpair} name1-name&maxpair;
array values {&maxpair} value1-value&maxpair;
input
  subsys
  month
  date
  time
  nr_blades
  %macro name_value;
  %do i = 1 %to &maxpair;
  name&i
  value&i
  %end;
  %mend;
  %name_value
;
do i = 1 to &maxpair;
  if names{i} ne '' then do;
    name = names{i};
    value = values{i};
    output;
  end;
end;
keep subsys month date time nr_blades name value;
cards;
53809,09,01-SEP-2015,0:15,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2
53809,09,01-SEP-2015,0:30,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2
53809,09,01-SEP-2015,0:45,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,3
53809,09,01-SEP-2015,1:00,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2
98909,09,01-SEP-2015,0:15,4,2MA,1,2MB,1,3ME,3,3MF,1,3MC,1,4MD,1,4MG,1,5MH,2
;
run;

proc transpose data=have out=want;
id name;
var value;
by subsys month date time nr_blades notsorted;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 78

Re: Transpose data using array or proc tranpose

Thanks, seems to work upto certain extend however when the value of name changes by subsys they are getting added as an extra column instead of replacing the names by subsys which crate issue as by subsys as names are very variable the row length with adding the columns are getting extended to very long length...

Super User
Posts: 6,938

Re: Transpose data using array or proc tranpose

Well, if you have n distinct names, you get n columns. Nothing surprising about that.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 78

Re: Transpose data using array or proc tranpose

Hi Kurt, in that case is it possible by SUBSYS we create the column heading for the names?

Super User
Posts: 6,938

Re: Transpose data using array or proc tranpose

Consider this method:

After initially reading the data into the long format, determine the distinct subsys's. (proc sort nodupkey)

Then create an empty output file with a data _null_;

Then work through the list of subsys's and use call execute to dynamically apply the transpose method for every single subsys, and add code in each iteration that appends a new header line and the data to the output file.

But you clearly cannot put all the data into one SAS dataset in wide format without getting a large number of columns.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,681

Re: Transpose data using array or proc tranpose

data have;
infile cards truncover dsd ;
input (SUBSYS Month Date Time Blades a b) ( : $20.) @;
n+1;
do while(not missing(a));
 output;
 input (a b) (: $20.) @;
end;
cards;
53809,09,01-SEP-2015,0:15,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2     
53809,09,01-SEP-2015,0:30,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2     
53809,09,01-SEP-2015,0:45,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,3     
53809,09,01-SEP-2015,1:00,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2
;
run;
proc transpose data=have out=want(drop=_name_);
by n SUBSYS Month Date Time Blades;
id a;
var b;
run;
Frequent Contributor
Posts: 78

Re: Transpose data using array or proc tranpose

Sorry this doesn't help...

Super User
Posts: 6,938

Re: Transpose data using array or proc tranpose

Then please post the desired output for

53809,09,01-SEP-2015,0:15,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
53809,09,01-SEP-2015,0:30,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
53809,09,01-SEP-2015,0:45,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,3    
53809,09,01-SEP-2015,1:00,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
98909,09,01-SEP-2015,0:15,4,2MA,1,2MB,1,3ME,3,3MF,1,3MC,1,4MD,1,4MG,1,5MH,2

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 78

Re: Transpose data using array or proc tranpose

Looking something like this, so with subsys change the blades name change as well...

 


Output Looking:
SUBSYS,MONTH,DATE,TIME,#Blades,1MA,1MB,1ME,1MF,2MC,2MD,2MC,2MH
53809,09,01-SEP-2015,0:15,8,1,1,2,1,1,1,1,2
53809,09,01-SEP-2015,0:30,8,1,1,2,1,1,1,1,2
53809,09,01-SEP-2015,0:45,8,1,1,3,1,1,1,1,3
53809,09,01-SEP-2015,1:00,8,1,1,3,1,1,1,1,2


SUBSYS,MONTH,DATE,TIME,#Blades,2MA,2MB,3ME,3MF,3MC,4MD,4MG,5MH
98909,09,01-SEP-2015,0:15,4,1,1,3,1,1,1,1,2

Super User
Posts: 9,681

Re: Transpose data using array or proc tranpose

So you need the uniform variable name no matter what it is in obs ?

 

data have;
infile cards truncover dsd ;
input (SUBSYS Month Date Time Blades x b) ( : $20.) @;
length a $ 20;
n+1;
idx=1;
a=cats('XX',idx);
do while(not missing(x));
 output;
 input (x b) (: $20.) @;
 idx+1;
 a=cats('XX',idx);
end;
drop idx x;
cards;
53809,09,01-SEP-2015,0:15,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2     
53809,09,01-SEP-2015,0:30,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2     
53809,09,01-SEP-2015,0:45,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,3     
53809,09,01-SEP-2015,1:00,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2
98909,09,01-SEP-2015,0:15,4,2MA,1,2MB,1,3ME,3,3MF,1,3MC,1,4MD,1,4MG,1,5MH,2
;
run;
proc transpose data=have out=want(drop=_name_);
by n SUBSYS Month Date Time Blades;
id a;
var b;
run;
Super Contributor
Posts: 259

Re: Transpose data using array or proc tranpose

[ Edited ]

Is it true, that your just want to swith column titles on channging "subsys"?

 

Try:

 

%let NumOfPairs = 8;

options mprint;

%macro NameValuePairs;
   %local i;
   %do i = 1 %to &NumOfPairs;
      Name&i Value&i
   %end;
%mend;

data work.have;

   length 
      subsys month date time no_blades 8
      Name1-Name&NumOfPairs $ 3
      Value1-Value&NumOfPairs 8
   ;

   informat date date11. time time6.;
   format month z2. date date11. time time6.;

   infile datalines delimiter=",";

   input subsys month date time no_blades 
      %NameValuePairs;
   ;

datalines;
53809,09,01-SEP-2015,0:15,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
53809,09,01-SEP-2015,0:30,8,1MA,1,1MB,1,1ME,2,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
53809,09,01-SEP-2015,0:45,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,3    
53809,09,01-SEP-2015,1:00,8,1MA,1,1MB,1,1ME,3,1MF,1,2MC,1,2MD,1,2MG,1,2MH,2    
98909,09,01-SEP-2015,0:15,4,2MA,1,2MB,1,3ME,3,3MF,1,3MC,1,4MD,1,4MG,1,5MH,2
;
run;


data _null_;
   set work.have;
   by subsys;

   length line $ 250;

   file "PATH-TO-FILE" delimiter=",";

   if first.subsys then do;
      line = catx(",", "SUBSYS,Month,Date,Time,#Blades", of name:);
      put line;
   end;

   put subsys month date time no_blades value1-value&NumOfPairs;
run;

 

Removes useless array definitions.

Ask a Question
Discussion stats
  • 13 replies
  • 383 views
  • 2 likes
  • 4 in conversation