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

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
LinusH
Tourmaline | Level 20
Take a look at the IN data set option. LOTS of examples our there if you search.
Data never sleeps
buechler66
Barite | Level 11

 

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

 

FreelanceReinh
Jade | Level 19

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

FinalData_2(in=b where= (rule_order < 997.1))
Tom
Super User Tom
Super User

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))
FreelanceReinh
Jade | Level 19

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

Use the IN= dataset option.

 

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

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
  • 6 replies
  • 1064 views
  • 4 likes
  • 4 in conversation