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 !
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 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.