DATA Step, Macro, Functions and more

merge files while adding new column

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

merge files while adding new column

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)


Accepted Solutions
Solution
‎10-16-2017 04:27 PM
Super User
Posts: 19,855

Re: merge files while adding new column

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;

View solution in original post


All Replies
Solution
‎10-16-2017 04:27 PM
Super User
Posts: 19,855

Re: merge files while adding new column

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;
Trusted Advisor
Posts: 1,022

Re: merge files while adding new column

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).

 

 

 

 

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 160 views
  • 4 likes
  • 3 in conversation