BookmarkSubscribeRSS Feed
rkumar23
Calcite | Level 5

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

13 REPLIES 13
Kurt_Bremser
Super User

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?

rkumar23
Calcite | Level 5

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

Kurt_Bremser
Super User

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;
rkumar23
Calcite | Level 5

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

rkumar23
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

Ksharp
Super User
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;
rkumar23
Calcite | Level 5

Sorry this doesn't help...

Kurt_Bremser
Super User

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

rkumar23
Calcite | Level 5

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

Ksharp
Super User

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;
andreas_lds
Jade | Level 19

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.

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
  • 13 replies
  • 1747 views
  • 2 likes
  • 4 in conversation