topic Re: Passing a SQL Query into a Macro in SAS Programming

Passing a SQL Query into a Macro

Serafim12 — Wed, 29 Jun 2016 11:07:24 GMT

I am trying to create a table with all the possible intersections of 4 tables in the same table with for example a colum that indicates the reference tables.

A with B

A with C

...

A with B with C with D

I can easily achive this but i dont want to create 15 tables and append in the end i would prefere to do this with a macro.

Is it possible?

Thanks for the help i will leave the proc sql that i use to create one of the intersects.

proc sql;
create table A as
select A.*,CAT(A.ID,' ',B.ID)AS MASTER_ID
from
work1 as A inner join
work2 as B on ...
run;

Re: Passing a SQL Query into a Macro

LinusH — Wed, 29 Jun 2016 12:32:45 GMT

You could probably solve it with a macro, but i can't see that it would be simple...

Perhaps there's another way?

Describe your requirement in more detail, like

Are A, B, C and D identical in structure?
What does each table represent (what differs them from the other ones)
What is the business meaning of an intersection?
How do you intend to use the result?

Re: Passing a SQL Query into a Macro

Cynthia_sas — Wed, 29 Jun 2016 12:44:15 GMT

And, in addition to LinusH suggestion for more information about the tables and the requirements, another consideration is that you are not really "passing an SQL query" INTO a Macro. A Macro program definition only types/generates code for you. So your Macro program would only generate the code. Then the generated code goes to the compiler. The fact is that before you start with a macro program definition to generate SQL code, you have to start with a working program and then "macro-ize" the working program so that when you run the macro program, it generates the code you want. And, as LinusH suggests, by the time you write the working SQL query to do what you want, you might find that you don't need a Macro program at all. The only use for a macro program in an instance like this would be do allow you to generate the code many, many times and possibly alter the code, based on some condition or parameter that you pass in. So far, you haven't explained either of those usage scenarios.

When I teach the Macro class I like to joke with my students that the SAS Macro facility is just a really big typewriter (if you remember those) and all it does is take symbolic parameters and conditional logic and make decisions that result in what code should be typed.

cynthia

Re: Passing a SQL Query into a Macro

Serafim12 — Wed, 29 Jun 2016 13:09:57 GMT

Yea in structure they are identical just have an ID and a description of the group and each table represents different groups, the final result is a table that have the Count of ID's for each possible combination so in the end i would have a table like

Combination	Count
A&B	100
A&C	200
A&B&C	50
…

Re: Passing a SQL Query into a Macro

Astounding — Wed, 29 Jun 2016 13:16:11 GMT

Since you plan on appending all the possible data sets back together again, you would be much better off with a single DATA step and a single variable to indicate matches/mismatches. Assuming that your data sets are sorted first:

data want;

merge a (in=in1) b (in=in2) c (in=in3) d (in=in4);

by id;

match = cats(of in1-in4);

run;