BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BIDD
Fluorite | Level 6

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)

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

 

View solution in original post

13 REPLIES 13
PeterClemmensen
Tourmaline | Level 20

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
Fluorite | Level 6
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
Fluorite | Level 6
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
Fluorite | Level 6
%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?
BIDD
Fluorite | Level 6
Hi, I just logged off, will see you morning
Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6
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
Super User

@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
Fluorite | Level 6

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
Super User Tom
Super User

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
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 5805 views
  • 0 likes
  • 6 in conversation