BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
8 REPLIES 8
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
SAS Employee
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
Pyrite | Level 9
@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
SAS Employee
hi Olivier,

i didn't get u !

plz describe !

pavan. Message was edited by: pavan
Olivier
Pyrite | Level 9
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
SAS Employee
very much happy with this explain !

thanks friend !

Gr8 !
Pavan_SAS
SAS Employee
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1062 views
  • 0 likes
  • 4 in conversation