topic Re: merge files while adding new column in SAS Programming

merge files while adding new column

okaka23 — Mon, 16 Oct 2017 15:31:53 GMT

Hello,

Is there a way to merge files by using SET while adding new columns, like this:

%let x=%str(XXX._XX_);
%let z=JUN2017;

%macro merge(y);
data b&y;
set
%do i=1 %to &y;
%let i2=%sysfunc(putn(&i,Z2));
&x.&i2.&z

date = "&i2.&z";
%end;;

run;
%mend;

%merge(10);

I am not sure where to put the red part, basically, I want to add a new Date column every time it go thru each SET (the original files dont have Date column in it, just marked in the file name)

Re: merge files while adding new column

Reeza — Mon, 16 Oct 2017 15:38:27 GMT

You can do that, but it may be easier to use the INDSNAME option instead.

This isn't a MERGE, it's an APPEND, since all data sets seem to have a common prefix you may be able to avoid a macro entirely.

data want;

set xxx._xx: indsname = source;

date = source;

run;

Re: merge files while adding new column

mkeintz — Mon, 16 Oct 2017 21:33:46 GMT

Probably all the incoming datasets have the same variables. If so, I recommend adding the "open=defer" option to the SET statement:

set xxx.xx_: indsname=source open=defer;

Instead of generating one buffer per incoming data set, this option re-uses the same buffer for each dataset. Saves memory, with no real performance cost. And if the OP has not only ddJUN2017, but also the other months (ddJAN2017 ... ddDEC2017), a lot of memory gets saved.

The only problem would be the result would not be in chronological order (since all datasets named XXX.XX_ are read in alphabetical order).