BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
serge68
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

2 REPLIES 2
WarrenKuhfeld
Rhodochrosite | Level 12
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;
PGStats
Opal | Level 21

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

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
  • 2 replies
  • 309 views
  • 0 likes
  • 3 in conversation