topic Macro to run SQL with multiple values in SAS Programming

Macro to run SQL with multiple values

Babloo — Thu, 22 Nov 2018 08:39:17 GMT

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.

Re: Macro to run SQL with multiple values

gamotte — Thu, 22 Nov 2018 08:54:34 GMT

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.

Re: Macro to run SQL with multiple values

RW9 — Thu, 22 Nov 2018 09:10:21 GMT

"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.

Re: Macro to run SQL with multiple values

Reeza — Thu, 22 Nov 2018 15:52:04 GMT

Here's a tutorial on making a macro:

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