Hello experts,
Please, I want to use sas macro -program in proc sql to automate the treatement, but I still have the errors
%let var1= tab;
%let var2=VR2;
%let var3=ctr_1;
I will do;
%marco test;
proc sql;
create table county_%substr(&var1.,1,2)_&var2_&var3. as
select catx('-',&var1,&var2.) as Id,
catx('-',&var1,&var2., &var3.) as section,
v4,
v5,
v6
from toto;
quit;
is it possible to put the below party in sas macro-program %init
create table county_%substr(&var1.,1,2)_&var2_&var3. as
select catx('-',&var1,&var2.) as Id,
catx('-',&var1,&var2., &var3.) as section,
and do
%macro test;
%init;
v4,
v5,
v6
from toto;
quit;
%mend test;
%test;
In that case write a proper macro with parameters.
%macro test(var1,var2,var3,varlist,memname);
%local dsn varlistsql;
%let dsn = county_%substr(&var1,1,2)_&var2._&var3 ;
%if %length(&varlist) %then
%let varlistsql = ,%sysfunc(translate(%sysfunc(compbl(&varlist)),',',' '))
;
proc sql ;
create table &dsn as
select catx('-',&var1,&var2) as Id
, catx('-',&var1,&var2,&var3) as section
&varlistsql
from &memname
;
quit;
%mend test ;
Then you could call like this:
%test(var1=A,var2=B,var3=C,varlist=w4 z8 y11,memname=beta)
%test(var1=A,var2=B,var3=C,varlist=m2 x2 l11,memname=labda);
Or even like this if you want to pass in the values of your existing VAR1 to VAR3 macro variables as the values to pass for those three parameters and if you didn't want to include any other variables from your source table.
%test(var1=&var1,var2=&var2,var3=&var3,varlist=,memname=labda);
That's not really clear.
What are you trying to do?
Some comments based on the code you've provided, see the comments. I'm really not sure how the second part aligns with your first set of code.
%let var1= tab; *create these as parameters to your macro;
%let var2=VR2;
%let var3=ctr_1;
I will do;
%marco test; *Macro vs marco - note spelling!;
proc sql;
create table county_%substr(&var1.,1,2)_&var2_&var3. as /*Dont use substr here, pre calculate it outside of the sql if necessary to help keep your code clean*/
select catx('-',&var1,&var2.) as Id,
catx('-',&var1,&var2., &var3.) as section,
v4,
v5,
v6
from toto;
quit;
*No %mend;?;
If you get error messages in the log post the log of the code ran and the error message. Post it to the forum in the code box to maintain formatting as the position of things can be of importance.
If the results are an error but not a code error then we need some example input data, the result data and what you expected should be the results.
Your existing code should be generating a WARNING about not resolving a VAR2_ . I think you meant to use:
create table county_%substr(&var1.,1,2)_&var2._&var3. ;
Note the . between &var2 and the _ to indicate the end of the macro variable name as &var2
I agree with @Reeza that use of %substr inline is not optimal coding style.
While I agree with those who would not code it that way, you can do what you are asking if you make a couple of changes:
@Astounding : Thank you.
I have tested your idea, it is correct, it is working.
I just add the below modifications. Please, why %init is correct but %init; is not correct ?
%let var1=alpha;
%let var2=beta;
%let var3=omega;
proc sql ;
create table county_%substr(&var1., 1, 2)_&var2._&var3. as
select catx('-', "&var1.","&var2.") as Id,
catx('-', "&var1.", "&var2.", "&var3.") as section,
Calling a macro with %init (or any other macro for that matter) does not require a semicolon. If you add one, it is not part of calling the macro. It is just an extra semicolon that is text, and becomes part of the SAS program. In most cases, adding an extra semicolon in the middle of a program does no harm. It just becomes an extra do-nothing SAS statement. In your case, however, the extra semicolon appears in the middle of a CREATE statement, ending that statement too early.
Your code will be much easier to understand and maintain if you move your macro defintions to before the block of code that will use them.
%macro init;
?????
%end;
%macro test;
%init
v4,v5,v6
from toto
;
quit;
%mend test;
proc sql ;
create table county_%substr(&var1.,1,2)_&var2_&var3. as
select catx('-',&var1,&var2.) as Id,
catx('-',&var1,&var2., &var3.) as section,
%test;
Since those macros do not appear to be doing any conditional logic why not just replace them with macro variables instead?
%let init= v1,v2,v3 ;
%let test= &init,v4,v5,v6 from toto ;
proc sql ;
create table county_%substr(&var1,1,2)_&var2._&var3 as
select catx('-',&var1,&var2) as Id
, catx('-',&var1,&var2,&var3) as section
, &test
;
quit;
@Tom : Thank you for your answer,
That's wight, but the matter is that
In one hand this section will be the same for all treatement
proc sql ;
create table county_%substr(&var1.,1,2)_&var2_&var3. as
select catx('-',&var1,&var2.) as Id,
catx('-',&var1,&var2., &var3.) as section,
in other hand, this section will change, it can be "w4,z8, y11 from beta " or "m2,x2,l11 from lambda"
That's why, I have used that méthode
v4,v5,v6
from toto
;
quit;
,
In that case write a proper macro with parameters.
%macro test(var1,var2,var3,varlist,memname);
%local dsn varlistsql;
%let dsn = county_%substr(&var1,1,2)_&var2._&var3 ;
%if %length(&varlist) %then
%let varlistsql = ,%sysfunc(translate(%sysfunc(compbl(&varlist)),',',' '))
;
proc sql ;
create table &dsn as
select catx('-',&var1,&var2) as Id
, catx('-',&var1,&var2,&var3) as section
&varlistsql
from &memname
;
quit;
%mend test ;
Then you could call like this:
%test(var1=A,var2=B,var3=C,varlist=w4 z8 y11,memname=beta)
%test(var1=A,var2=B,var3=C,varlist=m2 x2 l11,memname=labda);
Or even like this if you want to pass in the values of your existing VAR1 to VAR3 macro variables as the values to pass for those three parameters and if you didn't want to include any other variables from your source table.
%test(var1=&var1,var2=&var2,var3=&var3,varlist=,memname=labda);
@Tom: that's right , it is a good idea.
Thank you.
So please, what's about the length of the varlist, if I have the hundrerd of variables ?
varlist
A macro variable can be 64K characters long. So you could easily have thousands of variables.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.