Macro to run SQL with multiple values

[Babloo]

I've one SQL which I'm currently executing multiple times with different values. For example, assume the SQL is executing with one condition (e.g. emp_id=123) and the next time I'm executing with emp_id=234 and so on to create multiple tables.

Can we achieve this task in macro to execute the SQL once by passing different values to create multiple tables? Any pointers to the documents will also helpful.

[gamotte · Accepted solution]

Hello,

%macro doSQL(id);

    SELECT *
    FROM aDataset
    WHERE emp_id=&id.;

%mend;

proc sql;

    %doSQL(123);

    %doSQL(456);

quit;

Why do you want to create separate datasets per id ? Depending on what you are trying to do, a single

request with a GROUP BY statement could be a better solution.

[RW9]

"to create multiple tables" - really not a good idea, that is not thinking "the SAS way".  Just create grouping variables in the data for each group, and then use those grouping data for further processing.  E.g. you could loop over:

- create dataset with subject data for 001

- print dataset with subject 001 data

- create dataset with subject 002

...

Or you could simply say:

- print big dataset by subject where subject in 001,002...

As such the question is moot, a change to the thought process removes the need for extra messy maintainable code.

[Reeza]

Here's a tutorial on making a macro:

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md