I'm parsing data from a txt file and formatting into a csv. Part of that, is to combine multiple observations into a single row. There is no common identifier between the two rows, so I'm using a line pointer #, which works fine when I have a one-to-one relationship between the lines.
However I have a situation where I have one-to many lines, and I can't seem to figure out how to get this to work.
My output csv should look like –
Date, Time, ValueA, ValueB, ValueC
When I have data like this I can get it to work –
input #1 Date $ 01-04
Time $ 06-09
#2 ValueA $ 01-06
ValueB $ 07-13
ValueC $ 15-21
;
put @1 date time ValueA ValueB ValueC;
datalines;
now time
Fred Wilma Pebbles
late time
Barney Betty BamBam
sure time
Dino Slate Green
;
I get the desired results of –
now time Fred Wilma Pebbles
late time Barney Betty BamBam
sure time Dino Slate Green
However my data looks like this –
datalines;
now time
Fred Wilma Pebbles
Gord Susy NoNoNo
late time
Barney Betty BamBam
sure time
Dino Slate Green
Sam Slate Green
Norm Slate Green
;
How am I able to ensure the Date & Time values are merged with each subsequent line? I want to end up with output looking like this –
now time Fred Wilma Pebbles
now time Gord Susy NoNoNo
late time Barney Betty BamBam
sure time Dino Slate Green
sure time Sam Slate Green
sure time Norm Slate Green
Thanks….
The only difference between the two types of record is the number of fields, so use that:
data want;
retain date time;
infile datalines missover;
length valueA valueB valueC $16;
input valueA -- valueC;
if missing(valueC) then do;
date = valueA;
time = valueB;
end;
else output;
datalines;
now time
Fred Wilma Pebbles
Gord Susy NoNoNo
late time
Barney Betty BamBam
sure time
Dino Slate Green
Sam Slate Green
Norm Slate Green
;
data x(keep=line);
length pre $ 40 line $ 100;
retain pre;
input;
w1 = scan(_infile_, 1);
if w1 in ('now', 'late', 'sure') then pre = _infile_;
else do; line = catx(' ',pre, _infile_); output; end;
datalines;
now time
Fred Wilma Pebbles
Gord Susy NoNoNo
late time
Barney Betty BamBam
sure time
Dino Slate Green
Sam Slate Green
Norm Slate Green
;
proc print; run;
The only difference between the two types of record is the number of fields, so use that:
data want;
retain date time;
infile datalines missover;
length valueA valueB valueC $16;
input valueA -- valueC;
if missing(valueC) then do;
date = valueA;
time = valueB;
end;
else output;
datalines;
now time
Fred Wilma Pebbles
Gord Susy NoNoNo
late time
Barney Betty BamBam
sure time
Dino Slate Green
Sam Slate Green
Norm Slate Green
;
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.