pass a dynamic variable value as the data set name for Macro

[deleted_user]

Hi,
I have a dynamic dataset which can be grouped by var1 and var2. I have to split the dataset to a number of different data set based on the group of var1 and var2.
After I split them, I need to do some data manipulation for each of those data set. since the name of those data sets are dynamic, I was trying to use a variable to get the name of the data set and pass it to the macro for data manipulation, but I couldn't find the right way to pass a variable as the data set name in the Macro.
Here is my sample code:
data _null_ ;
set Overall;
subGroup="g" ||compress(var1,' ') ||"c"||compress(var2,"-") ;
call execute ( "data " || subgroup||";" ||
"set Overall ( where =( group = " || put(var1,1.) || " and centre = '" || put(var2,7.) || "'));" ||
"run ;" ) ;
%CalByCentre(subgroup);**********Subgroup won't be resolved
run ;
%Macro CalByCentre(&dsn);
data subVis;
set &dsn;
if sub_visit=1 and group=1 then do;
output LTsubVis;
end;
run;
%mend CalbyCentre;

Any help will be really appreciated!
Cathy

[Patrick]

Hi Cathy

There are so many issues with the code you provide that I wouldn't know where to start.

I thought it might be more helpful to give you a code example which could serve you as starting point to code what you need.

HTH
Patrick

data have;
do var1=1 to 3;
do var2=1 to 2;
othervar=var1*var2;
output;
end;
end;
run;

/* create macro var with list of dataset names */
proc sql;
select distinct cats('DS_',var1,'_',var2) into :DSList separated by ' '
from have;
quit;

/* macro creating SAS code block to output data into selected target data sets */
%macro SelectDS;
%let i=1;
%do %while (%scan(&DSList,&i,' ') ne );
%let DSname=%scan(&DSList,&i,' ');
if var1=%scan(&DSname,2,'_') and var2=%scan(&DSname,3,'_') then output &DSname %str(;)
else
%let i=%eval(&i+1);
%end;

/* finish if... then... else... block and catch undefined cases */
output UndefinedCases;

%mend;


/* process source data and output into target data sets */
data &DSList UndefinedCases;
set have;
/* do some processing here */
/* ....................
.................... */

/* generate SAS statements for output to target data sets */
%SelectDS
run;

[SAS_user]

I think, the problem is here:

When macro starts, the table subgroup is not still created, because call execute submits only after "run;" statement.

Try this:
data _null_ ;
set Overall;
subGroup="g" ||compress(var1,' ') ||"c"||compress(var2,"-") ;
call execute ( "data " || subgroup||";" ||
"set Overall ( where =( group = " || put(var1,1.) || " and centre = '" || put(var2,7.) || "'));" ||
"run ;" ) ;
run ;

%CalByCentre(subgroup);**********Subgroup won't be resolved;

[LinusH]

1) Put your macro definition in front of the data step
2) Macro calls won't execute within a data step. So you need to use call execute for that one as well: call execute("%CalByCentre("||subgroup||");");
/Linus

[Flip]

Actually the macro call would execute where it is, but the result would be far from what is expected.

If we resolve the first pass at the macro we would get:
data _null_ ;
set Overall;
subGroup="g" ||compress(var1,' ') ||"c"||compress(var2,"-") ;
call execute ( "data " || subgroup||";" ||
"set Overall ( where =( group = " || put(var1,1.) || " and centre = '" || put(var2,7.) || "'));" ||
"run ;" ) ;
*********** SAS would assume a "RUN;" here.
data subVis;
set subgroup;
if sub_visit=1 and group=1 then do;
output LTsubVis;
end;
run;
run;

so the SAS system would see a second Data step, assume a run; stop the execution of the Data _null_ ; and process data subVis. The macro would process there if it contained code which would execute within the data _null_.

[deleted_user]

Thanks for all the help.
I think Linus is right, I need to do "call execute ", otherwise the Macro only run once inside the data step. After I used the "Call execute", it worked for me. But this morning, when I tried to run again, I got an error message" The quoted string currently being processed has become more than 262 haracters long. You may have unbalanced quotation marks". It's the same program worked for me yesterday, any thought about this?
Thanks.

Cathy

[Flip]

When creating Call Execute statements it is quite often usefull to create a string first so that you can examine exactly what you are sending to the call execute.

You can do this by createing a temporary variable and using CALL EXECUTE(tempver);

or just do it in development and copy the code into the call execute.

[deleted_user]

Thanks for the tips. That will help to solve the string definitely.
I think I knew my problem about that error message is because for some reason my Macro wasn't considered end even though I do have "%mend; " statement somewhere. Because the macro wasn't end so the other data steps outside are still considered as part of the Macro. very Weird.

[Patrick]

As Linus already said:
You have to put your macro definition in front of the data step. The way the code is designed now you're calling the macro before you compile it.
If you're running your code in batch then it will never work, if it's a session then the code will fail the first time but then run in all consecutive trials (as now the macro got compiled into the sas macro catalogue in WORK).