merging problem

[deleted_user]

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?

[sbb]

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.

[deleted_user]

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!

[Pavan_SAS]

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;

[Olivier]

@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

[Pavan_SAS]

hi Olivier,

i didn't get u !

plz describe !

pavan. Message was edited by: pavan

[Olivier]

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

[Pavan_SAS]

very much happy with this explain !

thanks friend !

Gr8 !

[Pavan_SAS]

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;