DATA Step, Macro, Functions and more

Passing a SQL Query into a Macro

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Passing a SQL Query into a Macro

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;


Accepted Solutions
Solution
‎06-29-2016 11:33 AM
Super User
Posts: 5,082

Re: Passing a SQL Query into a Macro

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;

View solution in original post


All Replies
Super User
Posts: 5,256

Re: Passing a SQL Query into a Macro

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?
Data never sleeps
SAS Super FREQ
Posts: 8,743

Re: Passing a SQL Query into a Macro

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
New Contributor
Posts: 2

Re: Passing a SQL Query into a Macro

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

 

CombinationCount
A&B100
A&C200
A&B&C50
 
Solution
‎06-29-2016 11:33 AM
Super User
Posts: 5,082

Re: Passing a SQL Query into a Macro

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 289 views
  • 2 likes
  • 4 in conversation