topic Re: Handling two files at the same time in SAS Programming

Handling two files at the same time

Narasimha_Kulkarni — Thu, 20 Jun 2013 14:05:47 GMT

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

Re: Handling two files at the same time

DanWALDO — Thu, 20 Jun 2013 16:14:44 GMT

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.

Re: Handling two files at the same time

DanWALDO — Thu, 20 Jun 2013 16:55:33 GMT

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;

Re: Handling two files at the same time

Narasimha_Kulkarni — Fri, 21 Jun 2013 03:37:00 GMT

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

Re: Handling two files at the same time

Ksharp — Fri, 21 Jun 2013 04:41:17 GMT

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

Re: Handling two files at the same time

Narasimha_Kulkarni — Fri, 21 Jun 2013 06:39:54 GMT

Thank you Ksharp, it works spotless

Re: Handling two files at the same time

sunilzood — Fri, 21 Jun 2013 07:40:52 GMT

Dear Sharp,

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

Re: Handling two files at the same time

Ksharp — Fri, 21 Jun 2013 08:50:50 GMT

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

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