BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
8 REPLIES 8
Patrick
Opal | Level 21
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
Calcite | Level 5
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
Tourmaline | Level 20
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
Data never sleeps
Flip
Fluorite | Level 6
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
Not applicable
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
Fluorite | Level 6
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
Not applicable
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
Opal | Level 21
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).

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
  • 1170 views
  • 0 likes
  • 5 in conversation