topic Re: Match Merge with all cases output in SAS Programming

Match Merge with all cases output

GN0001 — Sat, 24 Jul 2021 05:51:11 GMT

data one;
 input ID $ Name $;
datalines;
A01 Sue
A02 Tom
A05 Key
A10 Jim
;
Run;

data two;
 input ID$ Age  sex $;
datalines;
A01 58 F
A02 20 M
A04 47 F
A10 11 M
;
Run;

Data all;
Merge one(in=in1) Two(in=in2);
By ID;
If in1=1 Then Output One;
if in2=1 then output two;
if (in1=0 and in2=1) then output inboth;
if (in1=0 and in2=1) then output NoMatch1;
if (in1=1 and in2=0) then output NoMatch2;
if (in1=1 OR in2=1) then output allrecs;
if (in1+in2=1) then output nomatch;
Run;

I have found this code in Support.sas.com and I tried to run it, either the sets is empty or data all is not shown in the output.

Regards,

blueblue

Re: Match Merge with all cases output

Kurt_Bremser — Sat, 24 Jul 2021 06:13:19 GMT

The last data step is syntactically incorrect and will not compile. Study the log and take corrective action.

You also have a semantic problem here:

if (in1=0 and in2=1) then output inboth;
if (in1=0 and in2=1) then output NoMatch1;

as the conditions are identical.

Note that the IN= variables are Boolean and can only have values of 0 (false) or 1 (true). The above condition can therefore be written as

if not in1 and in2

In Boolean logic, NOT is interpreted first, then AND, and finally OR.

Re: Match Merge with all cases output

ballardw — Sat, 24 Jul 2021 06:20:09 GMT

Do you look at the log?

Your second data step throws many errors:

48   Data all;
49   Merge one(in=in1) Two(in=in2);
50   By ID;
51   If in1=1 Then Output One;
                          ---
                          455
52   if in2=1 then output two;
                          ---
                          455
53   if (in1=0 and in2=1) then output inboth;
                                      ------
                                      455
54   if (in1=0 and in2=1) then output NoMatch1;
                                      --------
                                      455
55   if (in1=1 and in2=0) then output NoMatch2;
                                      --------
                                      455
56   if (in1=1 OR in2=1) then output allrecs;
                                     -------
                                     455
57   if (in1+in2=1) then output nomatch;
                                -------
                                455
ERROR 455-185: Data set was not specified on the DATA statement.

58   Run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ALL may be incomplete.  When this step was stopped there were 0
         observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

To use the name of a data set as the target of an OUTPUT statement the name must be on the DATA statement.

Since NONE of the data sets used as a target of the OUTPUT appear on the DATA statement the data step does not even execute.

If you expect to write to multiple data sets the data statement would look like:

Data allrecs one two inboth nomatch1 nomatch2 nomatch ;

Also when any explicit output statement is used the automatic write at the end of the data step is disabled as SAS has been told by you that you "know" when and which data set to write to.

We won't go into the bit about the potential problems with writing out to the same data sets on a set/merge statement at this time.

Re: Match Merge with all cases output

Sajid01 — Sat, 24 Jul 2021 17:35:03 GMT

Hello @GN0001

1. Dataset all will not have any data because, you are using output and there is no output all in your code.

2.As pointed out by @ballardw your data statement should be

Data allrecs one two inboth nomatch1 nomatch2 nomatch ;

3.The original datasets one and two will be overwritten by the last data step.

Re: Match Merge with all cases output

GN0001 — Sat, 24 Jul 2021 21:23:13 GMT

Hello,

If I want to name my dataset as all, then what would be the final? I want output to identify where the record is coming from:

data one;
 input ID $ Name $;
datalines;
A01 Sue
A02 Tom
A05 Key
A10 Jim
;
Run;

data two;
 input ID$ Age  sex $;
datalines;
A01 58 F
A02 20 M
A04 47 F
A10 11 M
;
Run;
Proc sort data=one;
by ID;
Run;

Proc sort data=two;
by ID;
Run;


Data All;
Merge one (in=one) Two (in=two);
By ID;
If in1=1 Then Output One;
if in2=1 then output two;
if (in1=1 and in2=1) then output inboth;
if (in1=1 and in2=0) then output NoMatch1;
if (in1=0 and in2=1) then output NoMatch2;
if (in1=1 OR in2=1) then output allrecs;
if (in1+in2=1) then output nomatch;
Run;

Respectfully,

blueblue

Re: Match Merge with all cases output

GN0001 — Sat, 24 Jul 2021 21:35:32 GMT

Hello team,

I changed the code to this:

data one;
 input ID $ Name $;
datalines;
A01 Sue
A02 Tom
A05 Key
A10 Jim
;
Run;

data two;
 input ID$ Age  sex $;
datalines;
A01 58 F
A02 20 M
A04 47 F
A10 11 M
;
Run;
Proc sort data=one;
by ID;
Run;

Proc sort data=two;
by ID;
Run;


Data one two inboth Nomatch1 nomatch2 allrecs nomatch;

Merge one (in=One) Two (in=two);
By ID;
If one=1 Then Output One;
if two=1 then output two;
if (one=1 and two=1) then output inboth;
if (one=1 and two=0) then output NoMatch1;
if (two=0 and one=1) then output NoMatch2;
if (one=1 OR two=1) then output allrecs;
if (one + two =1) then output nomatch;
Run;

it runs and gives the result. I want to capture all the cases in one dataset. Each record should be identified to each case belong to. This result can't be used for further step.

Regards,

blueblue

Re: Match Merge with all cases output

Sajid01 — Sat, 24 Jul 2021 22:42:12 GMT

In order to capture all observations in one row and also capture the case, then please modify the last step as follows.
The Status variable represents the case.

Data all;
Retain ID Name Age Sex Status;
Length Status $ 15;
Merge one (in=One) Two (in=two);
By ID;
If one=1 Then  Status="One";
if two=1 then Status="Two";
if (one=1 and two=1) then Status= "Inboth";
if (one=1 and two=0) then Status= "NoMatch1";
if (two=0 and one=1) then Status= "NoMatch2";
if (one=1 OR two=1) then Status= "Allrecs";
if (one + two =1) then Status= "Nomatch";
Run;

Re: Match Merge with all cases output

ballardw — Sun, 25 Jul 2021 09:07:43 GMT

@GN0001 wrote:

Hello team,

I changed the code to this:

data one;
 input ID $ Name $;
datalines;
A01 Sue
A02 Tom
A05 Key
A10 Jim
;
Run;

data two;
 input ID$ Age  sex $;
datalines;
A01 58 F
A02 20 M
A04 47 F
A10 11 M
;
Run;
Proc sort data=one;
by ID;
Run;

Proc sort data=two;
by ID;
Run;


Data one two inboth Nomatch1 nomatch2 allrecs nomatch;

Merge one (in=One) Two (in=two);
By ID;
If one=1 Then Output One;
if two=1 then output two;
if (one=1 and two=1) then output inboth;
if (one=1 and two=0) then output NoMatch1;
if (two=0 and one=1) then output NoMatch2;
if (one=1 OR two=1) then output allrecs;
if (one + two =1) then output nomatch;
Run;

it runs and gives the result. I want to capture all the cases in one dataset. Each record should be identified to each case belong to. This result can't be used for further step.

Regards,

blueblue

Since your data sets are small you need to show us what you expect for the output. "This result can't be used for further step" doesn't tell us what you expect to do with a further step or what is wrong with the output.

Re: Match Merge with all cases output

Tom — Mon, 26 Jul 2021 17:22:19 GMT

@Sajid01 has found the main problem with the original program. If you want an indicator of which inputs contributed to this observation you need to store that information into a variable, not use the OUTPUT statement. The OUTPUT statement is for writing observations to datasets.

But the logic of the flags needs to be cleaned up. Since there are two input datasets there are only three possible combinations of the IN= variables (if both were false then there would be no observation).

Here is simplified logic to capture the three possible combinations.

data all;
  retain ID Name Age Sex Status;
  length Status $15;
  merge one (in=One) Two (in=two);
  by ID;
  if not two then Status="One only";
  else if not one then Status="Two only";
  else Status= "Both";
run;

Re: Match Merge with all cases output

GN0001 — Mon, 26 Jul 2021 17:22:34 GMT

Hello,

I want all the cases captured in one data set.

I thought I can append the different cases.

What do you think?

Regards,

blueblue

Re: Match Merge with all cases output

Tom — Mon, 26 Jul 2021 19:29:31 GMT

@GN0001 wrote:

Hello,

I want all the cases captured in one data set.

I thought I can append the different cases.

What do you think?

Regards,

blueblue

If you MERGE two datasets all cases will be in the output, unless you do something to exclude them. The other possibility is that you want to do what is called an N-to-M join or combination where both N and M are larger than one. That is some of the key values are repeated in both of the source datasets. In that case the MERGE statement will just match them observations by observation and the result will be max(N,M) observations for those values of the BY variables. If instead you used SQL to do an full join then you would match each observation from one dataset with every observation from the other dataset. So you would get N*M observations instead.

For your small example show what you want to produce to clarify what you want.

Re: Match Merge with all cases output

GN0001 — Tue, 27 Jul 2021 03:30:21 GMT

Thanks for the response.

data all;
set one two inboth Nomatch1 nomatch2 allrecs nomatch;
Run

I want to append all the results into one set. If this is doable? I don't need a full join.

Regards,

blueblue

Re: Match Merge with all cases output

GN0001 — Tue, 03 Aug 2021 22:34:40 GMT

Hello team,

A while ago, I posted a question, which I couldn't get what I needed per level of my knowledge.

I will place a code for other users:

data out.mydata;
merge thisdata(in=a) thatdata(in=b);
by hkey;
format find $15.;
if a and b then Find = 'Both';
if a and not b then Find = 'not_in_thisdata'
if a then output;
run;

Regards,

blueblue

Re: Match Merge with all cases output

SASKiwi — Tue, 03 Aug 2021 23:08:54 GMT

So what is your question regarding this program? If you comment out - if a then output - all data from both MERGE datasets will be output.

Re: Match Merge with all cases output

GN0001 — Wed, 04 Aug 2021 06:26:21 GMT

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

Re: Match Merge with all cases output

Kurt_Bremser — Wed, 04 Aug 2021 07:27:30 GMT

@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.

Re: Match Merge with all cases output

GN0001 — Tue, 10 Aug 2021 23:43:59 GMT

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

Re: Match Merge with all cases output

SASKiwi — Tue, 10 Aug 2021 23:53:48 GMT

@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?

Re: Match Merge with all cases output

Tom — Wed, 11 Aug 2021 02:50:34 GMT

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

Re: Match Merge with all cases output

GN0001 — Wed, 11 Aug 2021 17:30:50 GMT

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