topic Re: Problems with Merge Statement and IN= Option in SAS Programming

Problems with Merge Statement and IN= Option

eap — Sat, 10 Dec 2016 01:17:50 GMT

Hi All,

(Sytax #1) I merged two datasets, got no errors and the dataset looks as expected (23 variables).

(Syntax #2) Next I wanted to make the IN= variables permanent. I got no errors but the merged dataset is exactly the same as the previous code where # of variables=23. To test that SAS was functioning I deliberately reversed the variables on either side of the equal sign and got an error.

(Syntax #3) Then I decided to create a ‘Match variable’ with 3 different categories. But the merge dataset is still identical to before and the new match variable was not added to the dataset.

SAS 9.4 recognizes if I insert an error an also if I use the 'If a' or 'If b' options. Condensed synytax is below.

What gives?

(SYNTAX #1)
DATA all_rates;
MERGE archived_data (in=a)
     recent_rates1 (in=b);
 by procedure_code procedure_type;
run;
NOTE: The data set WORK.ALL_RATES has 17842 observations and 23 variables.


(SYNTAX #2)
DATA all_rates ;
MERGE archived_data (in=a)
     recent_rates1 (in=b);
by procedure_code procedure_type;
    archived=a;
    recent=b;
run;
NOTE: The data set WORK.ALL_RATES has 17842 observations and 23 variables.


(SYNTAX #3)
DATA all_rates ;
MERGE archived_data (in=a)
     recent_rates1 (in=b);
by procedure_code procedure_type;
     if a and b then match='match';
     if a and not b then match='archived';
     if b and not a then match='recent';
run;
NOTE: The data set WORK.ALL_RATES has 17842 observations and 23 variables.

Re: Problems with Merge Statement and IN= Option

Patrick — Sat, 10 Dec 2016 02:36:47 GMT

The only explanation for not getting additional variables is, that these variables already exist in one of your source data sets; else everything looks totally fine with your code.

Re: Problems with Merge Statement and IN= Option

Kurt_Bremser — Sat, 10 Dec 2016 07:30:10 GMT

Kurt's maxim #1 for effective SAS programmers: Know Your Data.

Run proc contents on your input datasets and look at the variable lists.

Re: Problems with Merge Statement and IN= Option

eap — Thu, 15 Dec 2016 02:52:54 GMT

Hey Guys,

Just wanted to give an update (albeit a little delayed....had lots of traveling). Thanks Kurt and Patrick, I had no idea what approach to take to start investigating. You both put me on a path to think differently about the data.

Well, the culpruit was two-fold. First, I did PROC CONTENTS and found that indeed one dataset had the wrong length and format for one of the sorting variables -not sure why I didn't get a warning or error. Lastly, once that was fixed I got an error about duplicates in the BY variables. Once these were fixed everything worked fine.