Handling two files at the same time

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Handling two files at the same time

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

Attachment

Accepted Solutions
Solution
‎06-21-2013 12:41 AM
Super User
Posts: 9,691

Re: Handling two files at the same time

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


All Replies
Occasional Contributor
Posts: 7

Re: Handling two files at the same time

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.

Occasional Contributor
Posts: 7

Re: Handling two files at the same time

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;

Occasional Contributor
Posts: 12

Re: Handling two files at the same time

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

Solution
‎06-21-2013 12:41 AM
Super User
Posts: 9,691

Re: Handling two files at the same time

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

Occasional Contributor
Posts: 12

Re: Handling two files at the same time

Thank you Ksharp, it works spotless

Contributor
Posts: 29

Re: Handling two files at the same time

Dear Sharp,

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

Super User
Posts: 9,691

Re: Handling two files at the same time

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

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 270 views
  • 3 likes
  • 4 in conversation