topic Data Merge Help in SAS Programming

Data Merge Help

buechler66 — Thu, 07 Apr 2016 16:05:07 GMT

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;

Re: Data Merge Help

LinusH — Thu, 07 Apr 2016 16:21:05 GMT

Take a look at the IN data set option. LOTS of examples our there if you search.

Re: Data Merge Help

Tom — Thu, 07 Apr 2016 16:22:07 GMT

Use the IN= dataset option.

data DATA_MERGED;
  merge QueryData FinalData(in=inFinalData);
  by imb_code;
  if not inFinalData;
run;

Re: Data Merge Help

buechler66 — Thu, 07 Apr 2016 16:58:34 GMT

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

Re: Data Merge Help

FreelanceReinh — Thu, 07 Apr 2016 17:02:11 GMT

All dataset options need to be specified in a single pair of parentheses:

FinalData_2(in=b where= (rule_order < 997.1))

Re: Data Merge Help

Tom — Thu, 07 Apr 2016 17:03:57 GMT

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))

Re: Data Merge Help

FreelanceReinh — Thu, 07 Apr 2016 18:05:42 GMT

Just in case you are not aware of the pitfalls of your WHERE condition (rule_order < 997.1):

Missing values of RULE_ORDER would satisfy this condition. Use (. < rule_order < 997.1) to exclude them. (Or even safer: (.z < rule_order < 997.1))
Depending on how RULE_ORDER received its values (calculation, transfer from a database, ...), it can happen that due to certain rounding or numeric representation issues a value which should actually be equal to 997.1 is in fact stored as a slightly smaller number. (Remedy: (.z < round(rule_order, 1E-9) < 997.1), but the rounding unit, 1E-9, might need to be adapted, depending on your data.) Please see the example below and what it writes to the log (at least on Windows systems):

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;