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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.