topic Re: Contents of the IN operator as a SAS macro in SAS Programming

Contents of the IN operator as a SAS macro

avbraga — Mon, 09 May 2016 16:27:22 GMT

Hi all,

any way we can put the contents of an IN operator in a SAS macro. I'm doing something like:

PROC SQL;
CREATE TABLE data1 AS
SELECT *
FROM dataset t1
WHERE t1.PAYTOCGVRNPI in ('1023027968','1750591632');
QUIT;

The thing is that I have to do many data pulls based on subsetting on the variable PAYTOCGVRNPI, which takes different values everytime. I'd like to put the content of teh IN operator in a macro, but I'm running into quoting issues.

Any idea on how I could proceed here?

Thanks you!

Re: Contents of the IN operator as a SAS macro

ballardw — Mon, 09 May 2016 16:39:16 GMT

Describe how your are setting your macro variable(s) for use in the in statement.

You example code would work with: (though the comma isn't needed)

%let Mvar = '1023027968','1750591632';

PROC SQL;
   CREATE TABLE data1 AS
   SELECT *
   FROM dataset t1
   WHERE  t1.PAYTOCGVRNPI in (&mvar);
QUIT;

Are you passing values to a macro as parameters, generating them from a data set or something else?

Re: Contents of the IN operator as a SAS macro

Reeza — Mon, 09 May 2016 16:45:12 GMT

How are you creating your macro variables? If its from a different table use the quote function to help get quotes in the data.

Can you do all at once, do you really need to create multiple subsets of data?

proc sql noprint;

select quote(paytocgvrnpi) into :select_list separated by " "
from my_table
where <your where condition>;
quit;

Re: Contents of the IN operator as a SAS macro

avbraga — Mon, 09 May 2016 17:01:20 GMT

Thx Ballard. I've tried this option, but I would like to put it in this format

%macro a (var1, var2, var3);

code....

%mend a;

where:

var1 = would change the name of the datasets being created.

var2 = would be the variable I described previously to you

var3 = would be a facility number

but again, when I do it this way the commas and the quoting becomes an issue.

and I have a list of these paytocgrvnpi codes on a excel file for each facility number.

Thanks for the assistance.

Re: Contents of the IN operator as a SAS macro

ballardw — Mon, 09 May 2016 17:35:08 GMT

@avbraga wrote:

Thx Ballard. I've tried this option, but I would like to put it in this format

but again, when I do it this way the commas and the quoting becomes an issue.

The commas are not needed unless you are still running SAS 9.0 (I think, don't remember exactly which releast but is quite awhile ago).

So don't include them. Or if you need the commas at some other stage make a separate variable with commas inside the macro.

And are you always using character values that need to be quoted or will you sometimes need to pass actual numeric values?

Re: Contents of the IN operator as a SAS macro

Astounding — Mon, 09 May 2016 17:42:24 GMT

It sounds like you are turning an easy problem into a difficult one. It's easy, assuming that you know how to turn your spreadsheet into a SAS data set containing a variable that holds facility numbers. (If you don't know that part, you can get help with that too, but that would be a separate question.)

The third paramter in your macro would be the name of the SAS data set holding facility numbers. Then your IN operator would look like this:

in (select distinct FACILITYNO from &var3)

Re: Contents of the IN operator as a SAS macro

avbraga — Mon, 09 May 2016 18:37:54 GMT

oh, this is interesting. a subquery within the query itself. Will try this approach. Thanks, Astounding.