Merging observations into a single one

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Merging observations into a single one

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


Accepted Solutions
Solution
‎11-06-2017 04:58 PM
Esteemed Advisor
Posts: 5,482

Re: Merging observations into a single one

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

View solution in original post


All Replies
SAS Super FREQ
Posts: 497

Re: Merging observations into a single one

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;
Solution
‎11-06-2017 04:58 PM
Esteemed Advisor
Posts: 5,482

Re: Merging observations into a single one

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    
;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 122 views
  • 0 likes
  • 3 in conversation