topic Re: sas macro program in proc sql in SAS Programming

sas macro program in proc sql

LineMoon — Thu, 01 Dec 2016 22:50:39 GMT

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,

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,

from toto;

quit;

%mend test;

%test;

Re: sas macro program in proc sql

Reeza — Thu, 01 Dec 2016 22:56:37 GMT

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

Re: sas macro program in proc sql

ballardw — Thu, 01 Dec 2016 23:16:33 GMT

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.

Re: sas macro program in proc sql

Astounding — Fri, 02 Dec 2016 04:12:44 GMT

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.

Re: sas macro program in proc sql

LineMoon — Sun, 04 Dec 2016 09:22:46 GMT

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

80 %mend test;

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

Re: sas macro program in proc sql

Astounding — Sun, 04 Dec 2016 12:25:10 GMT

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.

Re: sas macro program in proc sql

Tom — Sun, 04 Dec 2016 17:18:09 GMT

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;

Re: sas macro program in proc sql

LineMoon — Sun, 04 Dec 2016 17:02:54 GMT

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

Re: sas macro program in proc sql

Tom — Sun, 04 Dec 2016 17:32:22 GMT

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

Re: sas macro program in proc sql

LineMoon — Sun, 04 Dec 2016 19:07:41 GMT

@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

Re: sas macro program in proc sql

Tom — Sun, 04 Dec 2016 22:18:40 GMT

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