topic Re: Macro Variable Query in SAS Programming

Macro Variable Query

scb — Wed, 07 Feb 2018 10:13:53 GMT

I expect the test dataset obtain the below result; instead it got message of variables not found in the contributing table. Anyone can help? Thanks.

Test Dataset

BRAND MODEL

Acura MDX

PROC SQL;
select MAKE, MODEL
into :BRAND, :MOD
from SASHELP.CARS WHERE MAKE EQ 'Acura' and MODEL EQ 'MDX';

PROC SQL;
CREATE TABLE TEST AS SELECT DISTINCT
&BRAND AS BRAND, &MOD AS MODEL FROM SASHELP.CARS;
QUIT;

Re: Macro Variable Query

Kurt_Bremser — Wed, 07 Feb 2018 10:48:58 GMT

After resolution of the macro variables, your second SQL will look like this:

PROC SQL;
CREATE TABLE TEST AS SELECT DISTINCT
Acura AS BRAND, MDX AS MODEL FROM SASHELP.CARS;
QUIT;

See? There are no variables Acura or MDX in sashelp.cars.

Maybe you are looking for a where condition?

PS stop shouting at your computer. The SAS interpreter can read lowercase perfectly well, and it's more readable for humans.

Re: Macro Variable Query

gamotte — Wed, 07 Feb 2018 11:27:45 GMT

In addition to @Kurt_Bremser's answer, there is no need to use intermediary macrovariables

proc sql;
    CREATE TABLE TEST AS
    SELECT MAKE AS BRAND, MODEL AS MOD
    FROM sashelp.cars
    WHERE strip(upcase(MAKE))='ACURA' AND strip(upcase(MODEL))='MDX';
quit;