DATA Step, Macro, Functions and more

Concatenate macro created datasets

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Concatenate macro created datasets

[ Edited ]

Hi,

 

I am a beginner and I have thousands of result data sets with similar 3 variables of Company Name, Return 1 and return 2, I would like to concatenate (as in merge vertically) all of them into one base file with no order required.

 

I used the following macro to merge them, but there seems to be many repeated observations. I have looked into this a long time but have not found the solution, I would really appreciate any suggestions.

 

 

%macro mergeresult(b=1);
%local j;
%do j = 1 %to &b;
data Work.BaseResult;
set Work.BaseResult Work.Number_&j._result;
run;
%end;
%mend;
%mergeresult(b=1000);

 


Accepted Solutions
Solution
‎02-12-2018 05:19 PM
Super User
Posts: 6,934

Re: Concatenate macro created datasets

This program is a good example of why you should focus first on SAS language, and wait until later to learn macro language.  This program runs for approximately 500 times as long as it needs to.  For example, you are running 1,000 DATA steps.  Each one has to read in then output the contents of NUMBER_1_RESULT data.  A better construction for the interior of the macro would look like this:

 

proc append base=work.BaseResult data=Work.Number_&j._Result;

run;

 

This program will also generate an error if the structure of one of the data sets is different than what you are expecting.  In this case, that would be a good result to point out differences instead of letting you combine that data set with the others.

View solution in original post


All Replies
Solution
‎02-12-2018 05:19 PM
Super User
Posts: 6,934

Re: Concatenate macro created datasets

This program is a good example of why you should focus first on SAS language, and wait until later to learn macro language.  This program runs for approximately 500 times as long as it needs to.  For example, you are running 1,000 DATA steps.  Each one has to read in then output the contents of NUMBER_1_RESULT data.  A better construction for the interior of the macro would look like this:

 

proc append base=work.BaseResult data=Work.Number_&j._Result;

run;

 

This program will also generate an error if the structure of one of the data sets is different than what you are expecting.  In this case, that would be a good result to point out differences instead of letting you combine that data set with the others.

Contributor
Posts: 22

Re: Concatenate macro created datasets

[ Edited ]
Posted in reply to Astounding

Thank you for your very much appreciated advice.

 

Edit: The code works perfectly. My bad for placing wrong syntax hence the errors. Thank you very much!

Super User
Posts: 24,010

Re: Concatenate macro created datasets

[ Edited ]

Modify your other macro to have the number at the end of the variable name. 

 

Work.Number_result_&j;

 

Then you can use the shortcut reference in the SET statement and no macro. 

 

EDIT: This post was posted incorrectly, but @Astounding

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 107 views
  • 2 likes
  • 3 in conversation