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
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
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.
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;
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
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
Thank you Ksharp, it works spotless
Dear Sharp,
Please Expalin the logic for id+1....
I make id variable to make them in the same group.
if it starts with '----' then we enter into another group (id+1).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.