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

Hi,

I'm trying to merge 2 files as explained below:

1st file contains some records then block of blank lines(the number of which can be controlled) and then some records (as below)

2nd file contains records seperated by some data seperator (in this file it is ----------)

I need the output as in 3rd file.

I have attached a text file along with this query which has details of 1st file, 2nd file & expecetd output.

Could you please hlep?

Narasimha Kulkarni

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data a;
input a $20.;
cards;
aaaaaa                                                                          
bbbbbb                                                                          
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
eeeeee                                                                          
ffffff                                                                          
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
gggggg   
;
run;
data b;
input a $20.;
cards;
111111                                                                          
222222                                                                          
------                                                                          
333333                                                                          
------ 
;
run;
data a(where=(a is not missing));
 set a;
 if not missing(a) and missing(lag(a)) then id+1;
run;
data b(where=(a is not missing));
 set b;
 retain id 1;
 if lag(a) eq: '----' then id+1;
run;
data want;
 set a b;
 by id;
run;


Ksharp

View solution in original post

7 REPLIES 7
DanWALDO
Calcite | Level 5

It would help greatly if you could explain better what you are trying to do. Based on your files, it appears that you wish to replace the blocks of "empty" records of file 1 with blocks of records from file 2, where the latter blocks are delimited by a series of dashes.

If that is the case, then it might be useful to process the first file with a counter to assign a sequential block number; then identify which blocks contain the blank records; and only output nonblank records. Then assign these block numbers sequentially to the blocks in file 2. Then you would SET the two files BY BLOCK_NUM.

DanWALDO
Calcite | Level 5

something like this, maybe.

data file1 gaps (keep=block_num);

  infile "mysas\file1.txt";

  retain block_num 1;

  length field previous $6;

  retain previous "x";

  input @1 field $6.;

  if field=" " and previous ne " " then block_num+1;

  else if previous=" " and field ne " " then block_num+1;

  if field ne " " then output file1;

  else output gaps;

  previous=field;

  drop previous;

  run;

proc sort data=gaps nodupkey;

  by block_num;

  run;

data file2 index (keep=block2);

  infile "mysas\file2.txt";

  retain block2 1;

  length field $6;

  input @1 field $6.;

  output;

  if field="------" then block2+1;

  run;

proc sort data=index nodupkey;

  by block2;

  run;

data index;

  merge index gaps;

  run;

data file2;

  merge index file2;

  by block2;

  drop block2;

  run;

data file3;

  set file1 file2;

  by block_num;

  run;

Narasimha_Kulkarni
Calcite | Level 5

Hi Dan,

Yes, I want the first block of blanks in the first file to be replaced by the first block of data records in second file (including dashes), second block of blank lines in first file with second block of records in second file and so on till end of both files

Hope I'm making some sense, please let me know if I'm not clear on this

Ksharp
Super User
data a;
input a $20.;
cards;
aaaaaa                                                                          
bbbbbb                                                                          
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
eeeeee                                                                          
ffffff                                                                          
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
gggggg   
;
run;
data b;
input a $20.;
cards;
111111                                                                          
222222                                                                          
------                                                                          
333333                                                                          
------ 
;
run;
data a(where=(a is not missing));
 set a;
 if not missing(a) and missing(lag(a)) then id+1;
run;
data b(where=(a is not missing));
 set b;
 retain id 1;
 if lag(a) eq: '----' then id+1;
run;
data want;
 set a b;
 by id;
run;


Ksharp

Narasimha_Kulkarni
Calcite | Level 5

Thank you Ksharp, it works spotless

sunilzood
Calcite | Level 5

Dear Sharp,

Please Expalin the logic for id+1....

Ksharp
Super User

I make id variable to make them in the same group.

if it starts with '----' then we enter into another group (id+1).

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
  • 7 replies
  • 904 views
  • 3 likes
  • 4 in conversation