DATA Step, Macro, Functions and more

Data Merge Help

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Data Merge Help

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;

Accepted Solutions
Solution
‎04-07-2016 02:10 PM
Super User
Super User
Posts: 7,074

Re: Data Merge Help

Posted in reply to buechler66

Use the IN= dataset option.

 

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

View solution in original post


All Replies
Super User
Posts: 5,437

Re: Data Merge Help

Posted in reply to buechler66
Take a look at the IN data set option. LOTS of examples our there if you search.
Data never sleeps
Regular Contributor
Posts: 212

Re: Data Merge Help

[ Edited ]

 

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

 

Trusted Advisor
Posts: 1,118

Re: Data Merge Help

Posted in reply to buechler66

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

FinalData_2(in=b where= (rule_order < 997.1))
Super User
Super User
Posts: 7,074

Re: Data Merge Help

Posted in reply to buechler66

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))
Trusted Advisor
Posts: 1,118

Re: Data Merge Help

Posted in reply to buechler66

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

  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))
  2. 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;
Solution
‎04-07-2016 02:10 PM
Super User
Super User
Posts: 7,074

Re: Data Merge Help

Posted in reply to buechler66

Use the IN= dataset option.

 

data DATA_MERGED;
  merge QueryData FinalData(in=inFinalData);
  by imb_code;
  if not inFinalData;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 280 views
  • 4 likes
  • 4 in conversation