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