topic Re: Merging observations into a single one in SAS Programming

Merging observations into a single one

serge68 — Sun, 05 Nov 2017 14:18:48 GMT

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

Re: Merging observations into a single one

WarrenKuhfeld — Sun, 05 Nov 2017 18:25:10 GMT

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;

Re: Merging observations into a single one

PGStats — Sun, 05 Nov 2017 19:09:29 GMT

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    
;