Help using Base SAS procedures

Creating new column with Macro Variables

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Creating new column with Macro Variables

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


Accepted Solutions
Solution
‎05-06-2015 03:53 PM
Super User
Posts: 10,497

Re: Creating new column with Macro Variables

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.

View solution in original post


All Replies
Solution
‎05-06-2015 03:53 PM
Super User
Posts: 10,497

Re: Creating new column with Macro Variables

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.

Contributor
Posts: 22

Re: Creating new column with Macro Variables

Hi Ballardw,

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 631 views
  • 0 likes
  • 2 in conversation