topic Re: Transpose data using array or proc tranpose in SAS Programming

Transpose data using array or proc tranpose

rkumar23 — Wed, 20 Jan 2016 07:10:29 GMT

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...

Re: Transpose data using array or proc tranpose

Kurt_Bremser — Wed, 20 Jan 2016 07:32:58 GMT

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?

Re: Transpose data using array or proc tranpose

rkumar23 — Wed, 20 Jan 2016 07:41:00 GMT

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

Re: Transpose data using array or proc tranpose

Kurt_Bremser — Wed, 20 Jan 2016 08:01:47 GMT

So we need to go the hard way 😉

%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;

Re: Transpose data using array or proc tranpose

Ksharp — Wed, 20 Jan 2016 08:41:19 GMT

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;

Re: Transpose data using array or proc tranpose

rkumar23 — Wed, 20 Jan 2016 08:58:34 GMT

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...

Re: Transpose data using array or proc tranpose

Kurt_Bremser — Wed, 20 Jan 2016 09:07:36 GMT

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

Re: Transpose data using array or proc tranpose

rkumar23 — Wed, 20 Jan 2016 09:08:04 GMT

Sorry this doesn't help...

Re: Transpose data using array or proc tranpose

Kurt_Bremser — Wed, 20 Jan 2016 09:10:04 GMT

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

Re: Transpose data using array or proc tranpose

rkumar23 — Wed, 20 Jan 2016 09:10:21 GMT

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

Re: Transpose data using array or proc tranpose

rkumar23 — Wed, 20 Jan 2016 09:13:51 GMT

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

Re: Transpose data using array or proc tranpose

Kurt_Bremser — Wed, 20 Jan 2016 09:16:30 GMT

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.

Re: Transpose data using array or proc tranpose

Ksharp — Wed, 20 Jan 2016 09:37:31 GMT

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;

Re: Transpose data using array or proc tranpose

andreas_lds — Wed, 20 Jan 2016 10:17:07 GMT

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.