DATA Step, Macro, Functions and more

merging problem

Reply
N/A
Posts: 0

merging problem

Hi all,

I have been struggling with the following problem, so please help me!

I have a 7,000 data sets with prefix and consecutive numbers (for example, xtmp_1, xtmp_2, ..., xtmp_7000. And I tried to merge all of these and I found a useful macro in this forum:


%macro names(prefix, begin, end);
%do i=&begin %to &end;
&prefix._&i
%end;
%mend;


First of all, when I execute the following code, it did not work:


data data; merge %names(xtmp, 1, 7000);
run;


SAS stops with an error message:

FATAL: Code generation error detected during MISSING smear generation.

Is that because the number of data set is too huge? Or is there any limit to the number of data sets SAS can merge in a single data procedure?
Super Contributor
Super Contributor
Posts: 3,174

Re: merging problem

No problem here (ThinkPad T61, Windows XP Pro SP3 with 4GB RAM) with merging 10,000 one-variable files and no BY statement. Your error message is a bit odd sounding, as you showed in your post. Start with a small subset of files, get that working, and then work your way up to a larger set of files for your merge, to confirm your process works.

Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: merging problem

It works fine with smaller set of data (less than 1,000). So, I had to write a macro to merge every 1,000 data sets first, and then merge the the 7 big data sets to a final one. But I still don't know what the error message means... Thanks anyway!
SAS Employee
Posts: 73

Re: merging problem

please use this code. hope it will work !

for better reason, using by statement(common variable) with merge is always good.

anyway, u can get result without by statement also !!!!!!


%macro names(prefix,begin,end);

data a;
length var $30.;
%do i=&begin. %to &end.;
var="&prefix._&i";
output;
%end;
run;

proc sql noprint;
select var into:names separated by ' '
from a;
quit;

proc datasets lib=work nolist nodetails;
delete a;
quit;

%mend;
%names(xtmp,1,7000);


data data;
merge &names.;
run;
Super Contributor
Posts: 260

Re: merging problem

@pavan : macro-variables have their length limited to some 32,000 characters in SAS version 8, and some 64,000 in version 9. Your solution handles 7,000 * 30 characters, which is way to large to succeed ! Some table names will be lost during the SQL macro-variable creation.
At least, if you want to do that, add LEFT(TRIM(var)) or STRIP(var) if you run the code in SAS 9 to throw away unnecessary blanks.

Regards.
Olivier
SAS Employee
Posts: 73

Re: merging problem

hi Olivier,

i didn't get u !

plz describe !

pavan. Message was edited by: pavan
Super Contributor
Posts: 260

Re: merging problem

If you define the VAR variable with a length of $30, it means that each value is stored on 30 characters. Even if the real value is shorter, blanks are added are the right of the value, so that the total length is 30 for each value, blanks included.
When you create the NAMES macro-variable, in the SQL query, you concatenate strings that are 30 characters long (in fact, 31, since there is an additional space added because of the SEPARATED BY " " option). DuRi, in his initial post, says he has 7,000 datasets : so here, the NAMES macro-variables will have to contain 7,000 * 31 characters = 217,000 characters (correct ?). Which is a lot more that any macro-variable can contain (see http://support.sas.com/documentation/cdl/en/mcrolref/59526/HTML/default/a002293823.htm ; no more than 65,534 characters).

Olivier
SAS Employee
Posts: 73

Re: merging problem

very much happy with this explain !

thanks friend !

Gr8 !
SAS Employee
Posts: 73

Re: merging problem

dear olievier,

then, is this code will work in the right way?

i am not bale to get this code. help me.

where this &prefix._&i is storing?

merge %names(xtmp, 1, 7000); is this correct?





%macro names(prefix, begin, end);
%do i=&begin %to &end;
&prefix._&i
%end;
%mend;


First of all, when I execute the following code, it did not work:


data data; merge %names(xtmp, 1, 7000);
run;
Ask a Question
Discussion stats
  • 8 replies
  • 205 views
  • 0 likes
  • 4 in conversation