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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.