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);
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.
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.
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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.