topic Re: Concatenate macro created datasets in SAS Programming

Concatenate macro created datasets

KrisDeng — Mon, 12 Feb 2018 22:07:01 GMT

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

Re: Concatenate macro created datasets

Astounding — Mon, 12 Feb 2018 22:12:25 GMT

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.

Re: Concatenate macro created datasets

Reeza — Mon, 12 Feb 2018 22:29:55 GMT

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

Re: Concatenate macro created datasets

KrisDeng — Mon, 12 Feb 2018 22:25:44 GMT

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!