T:SAS/ Proc SQL - select into: not in:

Question

I currently have a proc contents datafile which looks like this:

DATA CONTENTS;
    INPUT NAME $;
    DATALINES;
        VARA
        VARB
        VARC
        VARD
        VARE
    ;
RUN;

and I'm looking to turn this last of variables into a macro variable like so

PROC SQL;
    SELECT NAME INTO: MACRO_VARIABLE
        SEPARATED BY " "
            FROM CONTENTS
                WHERE VARNUM > 1
                AND
                WHERE NAME NOT IN:(VARA VARB)
;
QUIT;

The new addition which I'm trying to fit is the

Where name not in:(VarA varB)

is there a way to do this as my VARA and VARB are user inputs from my controller page, I can't specify them to be comma delimited as they will be "group_by" variables later in the script.

EDIT:

Imagine I have a macro variable in my controller

%LET group_by_variable = VARA VARB;

Then I do the same routine but substitute the macro variable like so

PROC SQL;
SELECT NAME INTO: MACRO_VARIABLE
    SEPARATED BY " "
        FROM CONTENTS
            WHERE VARNUM > 1
            AND
            WHERE NAME NOT IN:(&group_by_variable.)
 ;
QUIT;

Tom · Accepted Answer · 2018-11-08T21:00:32.670

In regular SAS statements you can use the colon modifier to do truncated string operations.

if  NAME NOT IN: ('VARA' 'VARB')

But in PROC SQL (and also WHERE statements) you cannot. So use the EQT operator instead.

WHERE NAME NOT EQT 'VARA'

You cannot use variables names with the IN operator. But it looks like you do not mean to reference variable names, but actual character strings. So you need to add quotes around the values.

It is not hard to add quotes around a space delimited list. You can do it with one function call if there is exactly one space between the values.

"%sysfunc(tranwrd(VARA VARB,%str( )," "))"

If your list of names is in a macro variable then use compbl() to make sure there is one space only.

%let mylist=%sysfunc(compbl(&mylist));

Josh Eller · Answer 2 · 2018-11-08T20:29:49.623

Yeah, you can do it, you've just got bad syntax in your query. I'd suggest looking up some guides on SAS SQL.

Firstly, you can only have one 'where' statement for each 'select' statement. Get rid of the second 'where', and just have the 'and'.

Secondly, drop the ':' after the 'in' statement.

Finally, add commas between the variables you're checking for.

PROC SQL;
    SELECT NAME INTO: MACRO_VARIABLE
        SEPARATED BY " "
            FROM CONTENTS
                WHERE VARNUM > 1
                AND
                NAME NOT IN (VARA, VARB)
;
QUIT;

If you can't add commas, just have SAS add the commas for you, by replacing all occurrances of the space character with a comma.

PROC SQL;
    SELECT NAME INTO: MACRO_VARIABLE
        SEPARATED BY " "
            FROM CONTENTS
                WHERE VARNUM > 1
                AND
                NAME NOT IN (
                    %sysfunc(tranwrd(%quote(VARA VARB),%str( ),%str(, )))
                )
;
QUIT;

Hi Josh, Thanks for the response but what I'm looking for is an answer which would either tell me that it's possible without the commma (,) or it is not possible without the comma, I've edited my question to be more explicit — , Nov 08 '18 at 20:26
@MattMcilwham OK, then you can just replace spaces with commas in the string. I've edited my answer to show how. — Josh Eller, Nov 08 '18 at 20:30

T:SAS/ Proc SQL - select into: not in:

2 Answers2