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 !
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.