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...
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?
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
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;
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...
Well, if you have n distinct names, you get n columns. Nothing surprising about that.
Hi Kurt, in that case is it possible by SUBSYS we create the column heading for the names?
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.
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;
Sorry this doesn't help...
Then please post the desired output for
53809,09,01-SEP-2015,0:15,8,1MA,1,1MB,1,1ME,2,1MF,
53809,09,01-SEP-2015,0:30,8,1MA,1,1MB,1,1ME,2,1MF,
53809,09,01-SEP-2015,0:45,8,1MA,1,1MB,1,1ME,3,1MF,
53809,09,01-SEP-2015,1:00,8,1MA,1,1MB,1,1ME,3,1MF,
98909,09,01-SEP-2015,0:15,4,2MA,1,2MB,1,3ME,3,3MF,
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
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.