Adding column to Existing Data Table in a Macro Step

[dwilliams1]

I am trying to add a column to a data table that was just created.  Prior to this I've connected to a database and extracted the files of interest.  The issue is that the lot ID is only contained in the file name and no where in the table itself. This will be an issue when I concatenate there files as I'll have no way distinguishing individual lots.  So, I'm trying to create a new column containing the lot id which is the value contained in ParamList for that iteration of the macro.  FYI, the sql step works fine. Any suggestions would be greatly appreciated.   Thank you!

options symbolgen mprint;

%macro loop;
 %do i=1 %to 10;
  proc sql;
   create table &&ParamList&i.._data as
   select *
   from Prod.&&ParamList&i.._DATA;
  quit;

  data &&ParamList&i.._data;
   set &&ParamList&i.._data;
   Run=%str(%'&&ParamList&i%');  /*  Here's where I get into trouble  */
 %end;
%mend;

%loop;

The following works fine but is completely manual and not realistic given the quantity I need to pull.

data xyz_data_data;
 set xyz_data;
 runs='xyz';
run;

[RW9]

That really is not a good way to model data, or to program, hence why you are having issues.  Do not create many datasets with the same data in to start with, this will just vastly increase the amount of programming needed and make your code slower and harder to maintain.  A good clue, if your having to do macro loops, then its likely your data is badly modelled.

A simple fix:

data param;
  length dsname $200;
  set prod.: indsname=tmp;
  dsname=tmp;
run;

With this it will set all datasets in prod together, and create a variable called dsname. You can now process all the data in one step, no loops or other code needed.  As I do not see what paramlist refers to I can't be more specific.

[Astounding]

I would replace this statement:

Run=%str(%'&&ParamList&i%');  /*  Here's where I get into trouble  */

Here's a better way:

Run="&&ParamList&i";

Be sure to use double-quotes, not single quotes.

In context, you might want to set a length for RUN, since it would be inconvenient to have it change length from one data set to the next.  So the program becomes:

data &&ParamList&i.._data;
   set &&ParamList&i.._data;
   length run $ 50;
   run = "&&ParamList&i";
run;

It's not clear if you want the new variable to be named RUN or RUNS, but that's easy to change if you want to.