DATA Step, Macro, Functions and more

sas macro program in proc sql

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

sas macro program in proc sql

[ Edited ]

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;

 

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎12-04-2016 01:58 PM
Super User
Super User
Posts: 6,502

Re: sas macro program in proc sql

[ Edited ]

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


All Replies
Super User
Posts: 17,963

Re: sas macro program in proc sql

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;?;
Super User
Posts: 10,550

Re: sas macro program in proc sql

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.

Super User
Posts: 5,099

Re: sas macro program in proc sql

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.
Super Contributor
Posts: 371

Re: sas macro program in proc sql

[ Edited ]

@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

 

Super User
Posts: 5,099

Re: sas macro program in proc sql

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.

Super User
Super User
Posts: 6,502

Re: sas macro program in proc sql

[ Edited ]

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;
Super Contributor
Posts: 371

Re: sas macro program in proc sql

[ Edited ]

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

Solution
‎12-04-2016 01:58 PM
Super User
Super User
Posts: 6,502

Re: sas macro program in proc sql

[ Edited ]

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);
Super Contributor
Posts: 371

Re: sas macro program in proc sql

[ Edited ]

@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

 

Super User
Super User
Posts: 6,502

Re: sas macro program in proc sql

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 349 views
  • 5 likes
  • 5 in conversation