copycolumn macro

[#1]

A little macro to copy the definition of a column from one dataset to another.

%macro copycolumn(column,fromset,toset);

proc sql noprint;

select type,format,informat into :type,:format,:informat from dictionary.columns
where catt(libname,'.',memname)= "&fromset"

and name="&column";

%put &=type &=format &=informat;

alter table &toset add &column &type format=&format informat=&informat;

%mend;

Here's an example:

%copycolumn(CLAIM_PAYMENT_STATUS, LIB2100.CLAIMS, LIB2100.NEWCLAIMS);

Adds CLAIM_PAYMENT_STATUS as a character column with a format of $2.

[Tom]

Not sure the value of this.  But you might need to add a lot of error checking to get it to work.  For example to make the query work. Also to handle variables that do not have FORMAT or INFORMAT defined. Note that most variables in SAS do not need to have either a FORMAT or an INFORMAT attached.

Why not just use normal SAS code instead of SQL code?

Then it is easy to extend it to do multiple variables at the same time.

%macro copycolumns(column_list,fromset,toset);
data &toset;
 set &toset &fromset(keep=&column_list obs=0);
run;
%mend;

[Ksharp]

proc sql;
create table class like sashelp.class;
quit;

[tomrvincent]

@Tom  @Ksharp My macro *adds* a single column to an existing dataset based on its definition in another dataset.  Both your posts describe creating a *new* dataset.  Not the idea at all.

[Tom]

How can you ADD a VARIABLE to an existing DATASET without re-writing the WHOLE dataset?  The ALTER TABLE is going to re-write the dataset also.

The advantage of the SQL ALTER TABLE approach is that it preserves dataset level metadata, like member label, indexes, constraints, etc. (other than member label something that is rarely needed in a SAS dataset).  But the macro requires updating to make it more user friendly in checking/using its inputs. For example to handle mixed case variable names, variables (columns) without attached formats and/or informats.

The advantage of the DATA step approach is that you don't really need to check the user inputs since they are just being used directly in the generated code and so SAS itself will check the code.

[tomrvincent]

@Tom it doesn't *require* updating.  You are certainly welcome to enhance it or ignore it as you see fit.  It works just fine for me and if, in the future, I find it needs additional coding, I'll deal with that if and when it comes up.