DATA Step, Macro, Functions and more

Macro Variable Query

Reply
Frequent Contributor
Frequent Contributor
Posts: 78

Macro Variable Query

[ Edited ]

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;

 

 

Super User
Posts: 9,890

Re: Macro Variable Query

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 331

Re: Macro Variable Query

In addition to @KurtBremser'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;
Ask a Question
Discussion stats
  • 2 replies
  • 81 views
  • 0 likes
  • 3 in conversation