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;
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.