Match Merge with all cases output

[Kurt_Bremser]

---

@GN0001 wrote:

Hello saskiwi;

Are you saying that 

if a and b then Find = 'Both';
if a and not b then Find = 'not_in_thisdata'

would be in the result?

Regards,

blueblue 

---

Maxim 4.

TRY IT.

[GN0001]

Hello,

I tried it, it give errors: 

data unmatched;
merge related1st a (in=a) related2st b (in=b);
by id;
format find $15.;
if a and not b then find="nomatch1";
if not a and b then find = "nomatch2";
run;

p.s. since morning I can't fix it. regards, blueblue

[SASKiwi]

@GN0001  - Saying your program gives gives errors but then not posting a SAS log including the errors will not get you answers any quicker.

You've listed four datasets in your MERGE statement including ones called A and B that perhaps should not be there. How many datasets do you really want to merge?

[Tom]

Let's look at your program line by line.

data unmatched;

So you are starting a data step with one output dataset named UNMATCHED.  Since it is a one level name it will normally be created in the WORK library.  (to learn about when it wouldn't be in WORK you can research the USER libref and USER option).

merge related1st a (in=a) related2st b (in=b);

So now you are telling it to merge 4 datasets named:  related1st,  a , related2st, and  b.  For two of them you have told it to create temporary variables named A and B to hold boolean flags that indicate if the dataset contributed to the current by group.

I suspect your don't actually have datasets named A and B.  Also I would not use A and B as the names for the temporary variables.  I like to use names that start with IN for those IN= flag variables.  It makes the code referencing them clearer.

So try using this line instead:

merge related1st (in=in1) related2st (in=in2);

Next you have:

by id;

This is saying that the merge should use the variable ID in both datasets to match the observations.  It also means that the observations in both datasets have to be in increasing order of value of ID (that is the data is sorted by ID).

Next you have:

format find $15.;

Which means you want SAS to use the $ format with a width of 15 when display the value of the FIND variable.  Normally there is no need (or reason) to attach a format to a character variable.  SAS already knows how to display character variables.  I think what you probable want here is to set the LENGTH of the variable FIND.

So use this statement instead:

length find $15 ;

Next you have these two IF statements:

if a and not b then find="nomatch1";
if not a and b then find = "nomatch2";

Note that you have not included an ELSE so both statements will run, but since it is impossible that both conditions can be true it does not cause any harm.  So using the new temporary variable names and including the ELSE that will become:

if in1 and not in2 then find="nomatch1";
else if not in1 and in2 then find = "nomatch2";

And finally you end the step with a RUN: statement.

So if this runs the variable FIND will contain three possible values.

'nomatch1       '
'nomatch2       '
'               '

The ones that are all blank are the observations where both datasets contained observation(s) with that value of ID.

The ones with nomatch1 are the ID values that only appear in related1st

The ones with nomatch2 are the ID values that only appear in related2st

[GN0001]

Hello,

I used a and b to alias my tables.

I removed a and b and my code ran without errors.

If we put two ifs then the second if wipes off the result of first if? Do we need to use end statement?

Regards,

blueblue

[SASKiwi · Accepted solution]

@GN0001  - You don't use aliases on MERGE statements you use the IN = option. Only SQL uses aliases. SAS is mistaking them for dataset names.

At the risk of repeating myself, you really, really, really should learn to read and understand the SAS documentation

I appreciate that the documentation is sometimes hard to follow for beginners but it is definitely worth the effort to understand it yourself. 

[GN0001]

Hello,

How do you know that I am not reading it? It is very hard to understand.
For example, for Merge statement, I found a white paper on the portal (SAS
community) and read that. Time is so limited and I have been given projects
and I need to finish it. SAS documents talk long about a concept without
introducing a syntax and giving examples.

Respectfully,

blue blue