Automating proc sql step using macro

[aayo]

Hi,

I want to know if it's possible to automate my proc sql step using macro. Below is my code but I am getting 2 datasets with same 'data a' content as result. I am supposed to have 2 different contents.

Kindly help.

%let sfile1 = a;

%macro varsum (sfile1);
proc sql;
create table &sfile1.
select *,
sum(x) as z,
from &file1
quit;
%mend;
/*calling the macro for the 2 datasets*/
%varsum(a);
%varsum(b);

below is my log

43 %varsum(a);
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.a_SUM created, with 10 rows and 14 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.04 seconds

44 %varsum(b);
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.b created, with 10 rows and 14 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

[Quentin]

Looks like you intend the from clause

from &file1

to be

from &sfile1

[aayo]

Hi Quentin,

No. I want to create 2 datasets -a and b from 2 existing files -file1 and file2 respectively

[Kurt_Bremser · Accepted solution]

Then you need to parameterize the input dataset also:

%macro varsum (sfile1,file1);
proc sql;
create table &sfile1.
select *,
sum(x) as z,
from &file1
quit;
%mend;
%varsum(a,file1)
%varsum(b,file2)

[aayo]

It worked. thanks.

[Astounding]

A couple of corrections on top of the (all good) advice you received so far.

Get rid of the first %LET statement.  It has absolutely no impact on the program, as you are assigning &SFILE1 a value when you call the macro.  All it does is add confusion by adding a value that the program never uses.

You don't show how the program gets a value for &FILE.  Where does that come from?

[aayo]

Hello Astounding,

I noticed that if i don't assign macro values, i don't get the desired output. I get one dataset as shown in the log below. The value of the second data overwrites the first, thereby giving me one dataset instead of 2 different datasets.

242 %totalsum (x,&file1);
NOTE: Table WORK.X created, with 10 rows and 8 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

243 %totalsum (y,&file2);
NOTE: Table WORK.X created, with 9 rows and 8 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

is there something different i can do?

[Tom]

Why are you referencing macro variables FILE1 and FILE2.

Shouldn't you just be passing the name of the actual files you want to read?  So if the files are named file1 and file2 then the code is like this:

%totalsum (x,file1);
%totalsum (y,file2);

And if the files are name SAM and FRED then the call looks like this:

%totalsum (x,SAM);
%totalsum (y,FRED);

[aayo]

Hi Tom,

file1 and file2 is going to be used a lot in my program and has been defined as a global macro.

%let helps me derive the desired output.

If not, how do I turn the following working code into an automatic macro that i can use to call other datasets without using %let first? lets say i have files 1,2,3 to use the macro for

proc sql;
select * from a
where x not in (select x from b );
quit;

[Tom]

I don't get the question at this point. Whether you have defined some global macro variables doesn't really matter. You use macro variables as replacements for strings of text in your code.

If you have some macro defined like this:

%macro totalsum (p1,p2);
 ...  &p1 ... &p2 ...
%mend ;

You can pass in any string you want as the values that P1 and P2 will have when the macro runs. So it can be constant text, Or text generated by expanding a macro variable, or some combination.

%totalsum(a,file1);
%totalsum(&mvar1,&mvar2);
%totalsum(prefix_&mvar1,&mvar2._suffix);

You can even have a global macro variable named P1 or P2. But just remember that they are different macro variables than the P1 and P2 that are local to the macro TOTALSUM.  So if you want TOTALSUM to use their values you do need to expand the macro variable in the call.  So these two calls are the same.

%let p1=a;
%let p2=file1;
....
%totalsum(&p1,&p2);
%totalsum(a,file1);

If you want to make that SQL query use the macro variables you defined before then just place the reference to the macro variables where you want to values used in the code.

So to recreate this statement:

select * from a where x not in (select x from b );

But with making A, X and B flexible you could do something like this. You might make 3 or 4 macro variables. Then replace the appropriate parts of the code with the macro variable references.

%let fromds=a;
%let var1=x;
%let var2=x;
%let excludeds=b;
...
select * from &fromds where &var1 not in (select &var2 from &excludeds);

Now to run for different input dataset or different variable(s) just change the values assigned to the macro variables in the %LET statements and re-run the code.

You could wrap that in a macro.  You could even define a macro that only uses one of those as an input parameter.  It could still hard code B for example. Or it could assume that EXCLUDEDS exists already and just reference that.  But make sure to place code/comment in the macro definition so you remember what it is doing.

%macro mymacro(varname);
%global fromds excludeds;
select * from &fromds where &varname not in (select &varname from &excludeds);
%mend;

%let fromds=a;
%let excludeds=b;
%mymacro(x);

[aayo]

Hi Tom,

I was doing the right thing but maybe I wasn't just clear enough.

Thanks for shedding more light to it anyway. I understand better now.

Cheers!