topic Re: Creating new column with Macro Variables in SAS Procedures

Creating new column with Macro Variables

JUN_Sands — Wed, 06 May 2015 19:42:26 GMT

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

Re: Creating new column with Macro Variables

ballardw — Wed, 06 May 2015 19:53:49 GMT

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.

Re: Creating new column with Macro Variables

JUN_Sands — Wed, 06 May 2015 20:11:32 GMT

Hi Ballardw,

'Alter Table' resolved the issue. Thanks so much !