I have two data sets both with the variable imb_code. I need to keep any records in QueryData that are not FinalData with the same imb_code.
Can someone help me with my Merge sytax for this problem?
Any help would be much appreciated.
proc sort data= QueryData;
by imb_code;
run;
proc sort data= FinalData;
by imb_code;
run;
data DATA_MERGED;
merge QueryData FinalData;
by imb_code;
Use the IN= dataset option.
data DATA_MERGED;
merge QueryData FinalData(in=inFinalData);
by imb_code;
if not inFinalData;
run;
I'm stuck here where I'm trying to filter FinalData to keep only records where rule_order is < 997.1 before the merge actually happens.
1378 data FinalData;
1379 merge QueryData (in=a) FinalData_2(in=b) (where= (rule_order < 997.1));
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, END, _DATA_, _LAST_, _NULL_.
ERROR 76-322: Syntax error, statement will be ignored.
1380 by imb_code;
1381 if a and not b;
1382 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.FINALDATA may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.FINALDATA was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
All dataset options need to be specified in a single pair of parentheses:
FinalData_2(in=b where= (rule_order < 997.1))
All of the dataset options go inside a single set of parentheses.
So if you want to filter the records coming from FINALDATA_2 you would use :
FinalData_2(in=b where= (rule_order < 997.1))
Just in case you are not aware of the pitfalls of your WHERE condition (rule_order < 997.1):
data demo;
x=997.3-0.2;
y=997.1;
if x<997.1 then put '###### Surprised? ######';
d=y-x;
run;
data _null_;
set demo;
put x= 32.28 / /* naive attempt to see what's in X */
x= best32. / /* another naive attempt */
x= hex16. / /* correct way to see what's in X */
y= hex16. / /* correct way to see what's in Y */
' ^' /
'Please note the difference in the last bit.' /
d=;
run;
Use the IN= dataset option.
data DATA_MERGED;
merge QueryData FinalData(in=inFinalData);
by imb_code;
if not inFinalData;
run;
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.