%macro for merge

[BIDD]

Hi, sorry guys, my previous message wasn't clear for you, so i am reposting it again.

I am working on project where i have to identify the type of crash and vehicles involved in the crash and some research involved. I have to do this for suburbs which comes under 70 km radius from metro.

Primary data set: Crash data which has only crash level info.

Secondary data: Units data which is more related type of vehicles and number of vehicles involved in a single crash and some other variables related to crash and vehicles.

Common ID or variable to identify the crash is acc_no

Likewise i will be merging other files like Traffic, Location, Person level, Injury level, Inventory level and got few other files.

I have to merge all these files with Crash data (Primary file). As i have to do it for over 60 suburbs and its very time consuming.

I was searching for the codes in internet and found out this code using Macro, i tried and failed. as i am getting all records of Units file getting merge with Crash and some error in Log.

%macro Mmerge(dest,source1,source2,var,cond);
%msort(&source1,&var);
%msort(&source2,&var);
data &dest;
merge &source1 (in=x) &source2 (in=y);
by &var;
&cond;
run;
%mend Mmerge;

%msort(iris.crash,acc_no);
%Msort(iris.units,acc_no);
%Mmerge(iris.crash_units,iris.crash,iris.units,acc_no,if x=1);
++++++++++++++++++++++++++++
Log as follows

32         %MSORT(CRASH,ACC_NO);
           _
           180
WARNING: Apparent invocation of macro MSORT not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro MSORT not resolved.
33         %MSORT(UNITS,ACC_NO;
           _
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR: More positional parameters found than defined.
34         %MMERGE(IRIS.CRASH_UNITS,IRIS.CRASH,IRIS.UNITS,ACC_NO,IF X=1)

[PeterClemmensen]

We can't see your MSORT macros from what you have posted. Post your MSORT MACROS and the full log.

Though, I fail to see why you would need a macro for this purpose.

[BIDD]

HI,
I am learning Macros, i am not used it. Macros wich i am trying is below
%MSORT10 (WORK.CRASH,ACC_NO);
%MSORT11(WORK.UNITS,ACC_NO);
%TEST5(IRIS.FINAL,WORK.CRASH,WORK.UNITS,ACC_NO,IF X=1);

[BIDD]

32 %MSORT(CRASH,ACC_NO);
_
180
WARNING: Apparent invocation of macro MSORT not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro MSORT not resolved.
33 %MSORT(UNITS_SORTED,ACC_NO;
_
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR: More positional parameters found than defined.
34 %MMERGE(IRIS.CRASH_UNITS_S,CRASH,UNITS_SORTED,ACC_NO,IF X=1);
35
36 GOPTIONS NOACCESSIBLE;
37 %LET _CLIENTTASKLABEL=;
38 %LET _CLIENTPROJECTPATH=;
39 %LET _CLIENTPROJECTNAME=;
40 %LET _SASPROGRAMFILE=;
41
42 ;*';*";*/;quit;run;
43 ODS _ALL_ CLOSE;
44
2 The SAS System 15:06 Thursday, November 16, 2017

45
46 QUIT; RUN;

[BIDD]

%MACRO MMERGE(DEST,SOURCE1,SOURCE2,VAR);
%MSORT(&SOURCE1,&VAR);
%MSORT(&SOURCE2,&VAR);
DATA &DEST;
MERGE &SOURCE1 &SOURCE2;
BY &VAR;
RUN;
%MEND MMERGE;
%MSORT(CRASH,ACC_NO);
%MSORT(UNITS_SORTED,ACC_NO;
%MMERGE(IRIS.CRASH_UNITS_S,CRASH,UNITS_SORTED,ACC_NO,IF X=1);

Log as follows:
1 The SAS System 15:06 Thursday, November 16, 2017

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROJECTPATH='';
5 %LET _CLIENTPROJECTNAME='';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13 STYLE=HtmlBlue
14 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15 NOGTITLE
16 NOGFOOTNOTE
17 GPATH=&sasworklocation
18 ENCODING=UTF8
19 options(rolap="on")
20 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 %MACRO MMERGE(DEST,SOURCE1,SOURCE2,VAR);
24 %MSORT(&SOURCE1,&VAR);
25 %MSORT(&SOURCE2,&VAR);
26 DATA &DEST;
27 MERGE &SOURCE1 &SOURCE2;
28 BY &VAR;
29 RUN;
30 %MEND MMERGE;
31
32 %MSORT(CRASH,ACC_NO);
_
180
WARNING: Apparent invocation of macro MSORT not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro MSORT not resolved.
33 %MSORT(UNITS_SORTED,ACC_NO;
_
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR: More positional parameters found than defined.
34 %MMERGE(IRIS.CRASH_UNITS_S,CRASH,UNITS_SORTED,ACC_NO,IF X=1)
35
36 GOPTIONS NOACCESSIBLE;
37 %LET _CLIENTTASKLABEL=;
38 %LET _CLIENTPROJECTPATH=;
39 %LET _CLIENTPROJECTNAME=;
40 %LET _SASPROGRAMFILE=;
41
42 ;*';*";*/;quit;run;
43 ODS _ALL_ CLOSE;
44
2 The SAS System 15:06 Thursday, November 16, 2017

45
46 QUIT; RUN;
47
Is this what you are asking?

[Kurt_Bremser]

In all your code, there is no definition of the macro MSORT, only call attempts.

[BIDD]

Hi, I just logged off, will see you morning

[Kurt_Bremser]

PS basic rule for all macro development:

- create Base code (without any macro reference) that works.

- look for things that have to be flexible

- replace those with macrovars, set the macrovars before the step, and test again until successful

- wrap code into a macro that has the macrovars as parameters, test again

- once that works, add macro logic in the macro if needed. Be aware that this step is most complicated, as you can't do a step-by-step test of code inside a macro.

Always use options mprint mlogic symbolgen; when testing macro code.

[RW9]

Some tips for posting questions.

Use a code window to post code, it is the {i} above the post window.

Provide the full code you are running in relation to your question, this is only one piece.

DO NOT CODE ALL IN UPPER CASE <- you see how rude it sounds, and how hard to read it is.

Macro is not a replacement for Base SAS.  If there is no reason to use it, do not use it, there is no reason I can see from your code why you would want to use macro for this problem - for instance, programmers know the sort procedure, they don't know your specific obfuscated msort10 or msort11 code, whatever that does.  Putting where clauses into macro will lead you down all kinds of problems.  

[BIDD]

Hi, sorry I am used to code in up case, so just copied and pasted it, will
b mindful next time posting..I am very new to SAS all leaning by my self
looking in to codes available in google...will check Tom morning regarding
this..I just logged off from my system

[ballardw]

---

@BIDD wrote:

Hi,

 I want to merge 2 data sets using Macro, I am using below codes:

 I want to merge UNITS file (960 records) with CRASH file(460 records) with common merging ID is ACC_NO.

And my final MERGE FILE (FINAL) should have 460 records. Whereas i am getting 960 records.

---

You really need to post some example data on what the starting data looks like and then what the result should be. From your table names this looks like you may have one data set with common traffic crash information such as date and time of crash, location, road or weather conditions and such. The "Units" set if that interpretation is correct might then be information about each car involved in the crash. If that is the case what would the "merged" data look like? You might be thinking of creating two variables for each field such as car make, model, license plate, driver etc. Generally that would be a poor choice for many types of analysis (mainly because you have some single car crashes, 2 car, 3 or more cars.

If that is what you want, you would have to reduce the 960 records to one record per ACC_N0 prior to merge with the CRASH dataset. MERGE will not do so. The resulting 960 record set is likely to be easier to work with in SAS.

[BIDD]

Hello All,

Just tried this way using Macro, it worked for me. 

I am happy to learn, if there is any other ways to do it.

%MACRO MSORT1(DATASET,VAR);
PROC SORT DATA=&DATASET;
BY &VAR;
RUN;
%MEND MSORT1;
%MACRO MSORT2(DATASET,VAR);
PROC SORT DATA=&DATASET;
BY &VAR;
RUN;
%MEND MSORT2;
%MACRO MMERGE1(DEST,SOURCE1,SOURCE2,VAR,COND);
DATA &DEST;
MERGE &SOURCE1 (IN=X) &SOURCE2 (IN=Y);
BY &VAR;
&COND;
RUN;
%MEND MMERGE1;

%MSORT1(WORK.CRASHES,ACC_NO);
%MSORT2(WORK.UNITS,ACC_NO);
%MMERGE1(WORK.CRASH_UNITS_CHECK5,WORK.CRASHES,WORK.UNITS,ACC_NO,IF X=1);

way comparing to this.

[Tom]

I assume you are just playing with writing macros to learn how they work?  Otherwise these macros do not appear to add much value. It takes almost as much code to call the macro as it does to just write the code they are generating to begin with.  And if you do not use macros then anyone can understand what you are doing without have to either guess what your macro does or find the macro definition and try to figure it out.

Also why did you write two macros that do the exact same thing?  With your current definitions these four sets of macro calls all do the same thing.

%MSORT1(WORK.CRASHES,ACC_NO);
%MSORT2(WORK.UNITS,ACC_NO);

%MSORT1(WORK.CRASHES,ACC_NO);
%MSORT1(WORK.UNITS,ACC_NO);

%MSORT2(WORK.CRASHES,ACC_NO);
%MSORT2(WORK.UNITS,ACC_NO);

%MSORT2(WORK.CRASHES,ACC_NO);
%MSORT1(WORK.UNITS,ACC_NO);

[ballardw · Accepted solution]

---

@BIDD wrote:

Hello All,

Just tried this way using Macro, it worked for me. 

I am happy to learn, if there is any other ways to do it.

way comparing to this.

---

You would be better off putting the required sort into the merge macro instead of requiring two different macro calls prior to the merge call. If you must have a macro to sort the data then use only one and call twice with the two data sources.

%MACRO MSORT1(DATASET,VAR);
PROC SORT DATA=&DATASET;
BY &VAR;
RUN;
%MEND MSORT1;

%MACRO MMERGE1(DEST,SOURCE1,SOURCE2,VAR,COND);

%msort1(&source1, &var);
%msort1(&source2, &var);

DATA &DEST;
MERGE &SOURCE1 (IN=X) &SOURCE2 (IN=Y);
BY &VAR;
&COND;
RUN;
%MEND MMERGE1;
%MMERGE1(WORK.CRASH_UNITS_CHECK5,WORK.CRASHES,WORK.UNITS,ACC_NO,IF X=1);

You may have difficulties with your Cond parameter as some things you put into an IF (or any other statement) may cause issues with the macro processor. For instance a comma as part of Cond will cause a "too many parameters found" error, or () will cause other errors. You may have to use macro quoting functions to pass code statements.