how to alter multiple datasets in same library and add a new column into them.

[Pandu]

Hi ,

Thank you in advance for your response.

I have a library in SAS and i have 50 datasets in it. below is an example

LIBNAME XXXXX SASSPDS
IP=YES LIBGEN=YES
HOST='Local' Serv='1111'
SCHEMA="xx0000" USER="xxx11spds"
PASSWORD="xxxxxx";

i want to add a new variable in all the 50 datasets.

1)employee.sas

2)batch.sas

3)task.sas

4)edge.sas

.

.

.

50)kohnge.sas

here i have written for one dataset. 

PROC SQL;
ALTER TABLE XXXXX.employee Add salary CHAR (10) Label='employeesalary';
QUIT;

But it will take lot of time to write for all 50 datasets like above.

Could you please help me in this scenario to write same code to update all the 50 datasets.

Br,

[SASJedi · Accepted solution]

A macro program can do this pretty easily, assuming you want to add the same column to all the datasets. Here is some working starter code:

/* This is just setting up some datasets to use for testing */
/* Make sure the WORK library is empty at the start */
proc datasets library=work kill nolist nodetails;
run; quit;

/* Copy in some datasets to work with */
proc copy in=sashelp out=work;
select class cars fish iris;
run;

/*****************************************************
 The work starts here 
*****************************************************/

/* Create a macro program to perform the bulk table alterations */
%macro AddColumn(libref,Name,Label,Length);
/* Get all of the table names into a series of macro variables */
proc sql noprint;
select memname 
into :table1-
from dictionary.tables
where libname ="%upcase(%superq(libref))";
;
quit;
%let numTables=&sqlobs;
/* Set up the alter table requirements */
%if &length = %then 
%let newCol=add &name num label="%superq(label)";
%else
%let newCol=add &name char(&length) label="%superq(label)";
/* add the column to all the tables */
proc sql;
%do i=1 %to &numTables;
ALTER TABLE &libref..&&table&i 
&newcol;
%end;
quit;
%mend;

/* Initial Table structure report */
title "Table Structure Before Modification";
ods  select Position;
proc contents data=work._all_ varnum;
run;
/* Use the macro to add a 10 character column named myNewCharCol */
%AddColumn(work,myNewCharCol,New Character Column,10)

/* Post Modification Table structure report */
title "Table Structure After myNewCharCol Add";
ods  select Position;
proc contents data=work._all_ varnum;
run;

/* Use the macro to add a numeric column named myNewNumeric */
%AddColumn(work,myNewNumeric,New Numeric Column)

/* Post Modification Table structure report */
title "Table Structure After myNewNumeric Add";
ods  select Position;
proc contents data=work._all_ varnum;
run;