topic Re: Merge and Keep option in SAS Programming

Merge and Keep option

Ranjeeta — Thu, 29 Nov 2018 16:58:36 GMT

data SP340_WITH_REG_TIME_PtLHIN nonmatches ;
merge sp340_wregtime_sorted(in=INSp340) DAD1718_sorted(in=INDAD)(KEEP=PATIENT_LHIN);
by CIHI_KEY;
if INSp340 =1 and INDAD=1 then output SP340_WITH_REG_TIME_PtLHIN;
ELSE output nonmatches;
run;

Can somone please advise how to write the keep option correctly in the merge statement

Re: Merge and Keep option

PeterClemmensen — Thu, 29 Nov 2018 17:11:47 GMT

Do it in the same parenthesis

data SP340_WITH_REG_TIME_PtLHIN nonmatches ;
merge sp340_wregtime_sorted(in=INSp340) DAD1718_sorted(in=INDAD KEEP=PATIENT_LHIN);
by CIHI_KEY;
if INSp340 =1 and INDAD=1 then output SP340_WITH_REG_TIME_PtLHIN;
ELSE output nonmatches;
run;

Re: Merge and Keep option

Ranjeeta — Thu, 29 Nov 2018 17:32:12 GMT

I get the error BY variable CIHI_KEY is not on input data set WORK.DAD1718_SORTED
but I see the variabe when I do proc contents Please advise

Re: Merge and Keep option

PeterClemmensen — Thu, 29 Nov 2018 17:36:10 GMT

If SAS says that a variable is not in a data set, it is not there. Period.

Please post the output from the contents procedure

Re: Merge and Keep option

FreelanceReinh — Thu, 29 Nov 2018 18:06:55 GMT

Hi @Ranjeeta,

With the KEEP= dataset option you exclude all other variables. So, you should add the BY variable to the keep list:
keep=patient_lhin cihi_key.

Re: Merge and Keep option

Ranjeeta — Thu, 29 Nov 2018 18:11:34 GMT

It was because i did not nclude the variable on keep option

Re: Merge and Keep option

Ranjeeta — Thu, 29 Nov 2018 18:13:20 GMT

There were 620 observations read from the data set WORK.SP340_WREGTIME_SORTED.
NOTE: There were 1197772 observations read from the data set WORK.DAD1718_SORTED.
NOTE: The data set EVT.SP340_WITH_REG_TIME_PTLHIN has 620 observations and 17 variables.
NOTE: The data set WORK.NONMATCHES has 1197174 observations and 17 variables.
NOTE: DATA statement used (Total process time):
real time 7.00 seconds
cpu time 3.23 seconds

Why am I getting 22 additional records

Re: Merge and Keep option

Ranjeeta — Thu, 29 Nov 2018 18:13:54 GMT

Why am i getting 22 additional recors in nonmatches

Re: Merge and Keep option

Tom — Thu, 29 Nov 2018 19:09:58 GMT

The totals should only add up if both datasets only have one observation per BY group.

Re: Merge and Keep option

Ranjeeta — Thu, 29 Nov 2018 20:33:23 GMT

thankyou

Re: Merge and Keep option

Ranjeeta — Thu, 29 Nov 2018 20:45:33 GMT

data EVTQBP EVTnotQBP;
merge EVT.EVT_hometime(in=INfullcohort) EVT.Evt_qbp(in=INQBP);
by CIHI_KEY;
if INfullcohort =1 and INQBP=0 then output EVTnotQBP;
ELSE output EVTQBP;
run;
EVT.Home time data set has 696 keys and EVT.Evt_qbp has 687 keys
I am trying to write out the 9 keys in the EVTnotQBP dataset and want to know if my code is doing it correctly?
Also in the EVTQBP dataset I want to write out the keys that are only present in the EVT.Evt_qbp dataset which has 687 observations
Is my code correct to get the desired output?

Re: Merge and Keep option

FreelanceReinh — Thu, 29 Nov 2018 21:32:05 GMT

There are up to three (mutually exclusive) possible cases for the pair (INfullcohort, INQBP): (1, 0), (0, 1) and (1, 1). You write the observations belonging to the first case to dataset EVTnotQBP (which makes sense), but EVTQBP will then in general contain observations with "keys that are only present in the EVT.Evt_qbp dataset" plus observations with keys that are present in both datasets.

Edit: Regarding your previous question:

For example, even if the 620 keys (CIHI_KEY values) in SP340_WREGTIME_SORTED were unique, it could be that each of 22 of them match 2 observations in DAD1718_SORTED while 22 other keys don't match any observation. Now, if the remaining 620-22-22=576 keys lead to one-to-one matches, you'll get 576*1+22*2=620 observations in the "intersection" (SP340_WITH_REG_TIME_PTLHIN). Finally, DAD1718_SORTED will contribute 1197772-620 observations to dataset NONMATCHES in addition to the 22 with non-matching keys from SP340_WREGTIME_SORTED, resulting in a total of 1197772-620+22=1197174 observations in NONMATCHES.

Re: Merge and Keep option

Ranjeeta — Thu, 29 Nov 2018 21:48:25 GMT

ok then how would i write the code to get only the EVT QBP cases

Re: Merge and Keep option

FreelanceReinh — Thu, 29 Nov 2018 22:51:12 GMT

Just insert the appropriate IF condition into the ELSE statement:

else if INfullcohort=0 and INQBP=1 then output EVTQBP;

Re: Merge and Keep option

Ranjeeta — Fri, 30 Nov 2018 14:39:03 GMT

Thankyou !!