BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LineMoon
Lapis Lazuli | Level 10

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;

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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);

View solution in original post

10 REPLIES 10
Reeza
Super User

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;?;
ballardw
Super User

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.

Astounding
PROC Star

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:

 

  • There has to be a PROC SQL statement somewhere.  You can't just add a CREATE statement in the middle of a program.
  • When calling a macro, do not add an extra semicolon.  %init is correct, but %init; is incorrect.
LineMoon
Lapis Lazuli | Level 10

@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,

 

74 %macro test;
75 %init
76 v4, v5, v6
77 from toto;
78 quit;
79
80 %mend test;
81
82 %test;
NOTE: Table WORK.COUNTY_AL_BETA_OMEGA created, with 1 rows and 5 columns.
 
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

 

Astounding
PROC Star

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.

Tom
Super User Tom
Super User

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;
LineMoon
Lapis Lazuli | Level 10

@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;

Tom
Super User Tom
Super User

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);
LineMoon
Lapis Lazuli | Level 10

@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

 

Tom
Super User Tom
Super User

A macro variable can be 64K characters long. So you could easily have thousands of variables.

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
  • 10 replies
  • 1235 views
  • 5 likes
  • 5 in conversation