Hello All,
I am trying to create a new column with using macro variables which were created by the beginning of program. But the macro variable doesn't show up as variable I assigned. It comes with "&location_name'
Or it gives me an error ERROR: The following columns were not found in the contributing tables
Here is a code I used...
Data location;
id=1; location_name='London'; sas_name='qqq'; output;
id=1; location_name='New York'; sas_name='www'; output;
id=1; location_name='Tokyo'; sas_name='eee'; output;
Run;
.....................
..............................
%macro process;
%let I=1;
%let sas_name=%scan(&sas_name_list, &1);
%let location_name=%scan(&location_name_list, &1);
%do %until(&sas_name= );
Proc SQL;
Create table &sas_name as
Select *, &location_name as loc_name
from work.&sas_name;
quit;
%let I=%val(&I +1);
%let sas_name = %scan(&sas_name_list, &I);
%let location_name = %scan(&location_name_list, &I);
%end;
%mend;
%process;
Please let me know if anything is unclear and thanks!
&location_name as loc_name
You may be having issues with
%let sas_name=%scan(&sas_name_list, &1);
I would expect an error as &1 (digit 1) is not valid for a macro variable name. I suspect that you mean &l.
Also this line
Select *, &location_name as loc_name
if you want to assign the text value of the macro variable location_name should be
Select *, "&location_name" as loc_name
if you want to assign the same value to every instance of loc_name in the output.
The ERROR: The following columns were not found in the contributing tables
is because with the "" around the macro variable SAS is looking for a variable named London whose values would be assigned to loc_name.
It might help to provide a small example starting data set and what you expect the output to be.
Trying to ADD variables to an EXISTING data set really should use the ALTER TABLE statement instead of CREATE TABLE.
You may be having issues with
%let sas_name=%scan(&sas_name_list, &1);
I would expect an error as &1 (digit 1) is not valid for a macro variable name. I suspect that you mean &l.
Also this line
Select *, &location_name as loc_name
if you want to assign the text value of the macro variable location_name should be
Select *, "&location_name" as loc_name
if you want to assign the same value to every instance of loc_name in the output.
The ERROR: The following columns were not found in the contributing tables
is because with the "" around the macro variable SAS is looking for a variable named London whose values would be assigned to loc_name.
It might help to provide a small example starting data set and what you expect the output to be.
Trying to ADD variables to an EXISTING data set really should use the ALTER TABLE statement instead of CREATE TABLE.
Hi Ballardw,
'Alter Table' resolved the issue. Thanks so much !
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.