topic Re: Correcting a merge dataset in SAS Programming

Correcting a merge dataset

wheddingsjr — Tue, 04 Aug 2020 03:32:00 GMT

Hi all. I am trying to duplicate SAS logic and get the same results that some else did previously merging two datasets. The first dataset (HPHC_EOL_FY20), has 85k lines and the second (EOL_COHORT), has 135 lines. In the original results that I am trying to duplicate the final result has 55k lines, when I run the below code I get 130k. I am no sure what I am doing wrong. Any advise would be appreciated.

PROC SQL;
create table HPHC_FY20_EOL_combined_v2 as
	select med.*,eol.*
		from Dataset1 as med
				left join
			 Dataset2 as eol
			 	on med.member_id=eol.INSUREDSIDNUMBER
		;
Quit;

Re: Correcting a merge dataset

Jagadishkatam — Tue, 04 Aug 2020 03:58:20 GMT

Without looking at the data, it is difficult to predict. However i assume the previous merge was done if both datasets had med.member_id=eol.INSUREDSIDNUMBER, considering this i would try inner join instead if left join. please try and let me know if it works

PROC SQL;
create table HPHC_FY20_EOL_combined_v2 as
	select med.*,eol.*
		from Dataset1 as med
				inner join
			 Dataset2 as eol
			 	on med.member_id=eol.INSUREDSIDNUMBER
		;
Quit;

Re: Correcting a merge dataset

wheddingsjr — Tue, 04 Aug 2020 05:15:47 GMT

Thanks for the response Jag. I tried the inner join and got the same results. I think I have to delete dupes, but not sure how to do that.

Re: Correcting a merge dataset

Kurt_Bremser — Tue, 04 Aug 2020 06:13:56 GMT

@wheddingsjr wrote:
Thanks for the response Jag. I tried the inner join and got the same results. I think I have to delete dupes, but not sure how to do that.

proc sort
  data=dataset2
  out=dedup
  nodupkey
;
by INSUREDSIDNUMBER;
run;

Re: Correcting a merge dataset

wheddingsjr — Tue, 04 Aug 2020 10:47:03 GMT

Thanks Kurt for the response, but that did not work

Re: Correcting a merge dataset

Kurt_Bremser — Tue, 04 Aug 2020 11:08:26 GMT

Please describe in detail what happened. "Did not work" is not helpful at all, and worthy of the proverbial blonde secretary.

how many observations are in the first dataset?
how many are in the second dataset before and after deduplicating (read the log)?
how many are in the result?

Mind that a left join will ALWAYS have at least the number of observations of the "left" dataset. To build the intersection set, you have to use an inner join.

Re: Correcting a merge dataset

wheddingsjr — Tue, 04 Aug 2020 12:37:30 GMT

Hi Kurt

This is what I included in my original post:

Hi all. I am trying to duplicate SAS logic and get the same results that some else did previously merging two datasets. The first dataset (HPHC_EOL_FY20), has 85k lines and the second (EOL_COHORT), has 135 lines. In the original results that I am trying to duplicate the final result has 55k lines, when I run the below code I get 130k. I am no sure what I am doing wrong. Any advise would be appreciated.

Re: Correcting a merge dataset

Kurt_Bremser — Tue, 04 Aug 2020 12:39:49 GMT

how many are in the second dataset before and after deduplicating (read the log)?

Re: Correcting a merge dataset

wheddingsjr — Tue, 04 Aug 2020 13:28:28 GMT

Hi Kurt

This the log:
NOTE: There were 136 observations read from the data set WORK.dataset2.
NOTE: 33 observations with duplicate key values were deleted.
NOTE: The data set WORK.DEDUP has 103 observations and 17 variables.
NOTE: PROCEDURE SORT used (Total process time):

Re: Correcting a merge dataset

Kurt_Bremser — Tue, 04 Aug 2020 13:45:33 GMT

And how many observations resulted from the join of the large table with your deduped dataset?

Do you need the deduped dataset to create a subset of the large table, or should all observations from the large table be kept?

Re: Correcting a merge dataset

wheddingsjr — Tue, 04 Aug 2020 14:07:03 GMT

There were still 130k lines..What I believe needs to happen is to delete all the dupes from the 130k

Re: Correcting a merge dataset

Kurt_Bremser — Tue, 04 Aug 2020 15:07:34 GMT

Did you change from a left join to an inner join? Please post the complete log of your current join step.

Re: Correcting a merge dataset

wheddingsjr — Tue, 04 Aug 2020 15:43:42 GMT

138 PROC SQL;
139 create table HPHC_FY20_EOL_combined_v2 as
140 select med.*,eol.*
141 from HPHC_EOL_FY20 as med
142 inner join
143 EOL_Cohort as eol
144 on med.member_id = eol.INSUREDSIDNUMBER
145 ;
NOTE: Table WORK.HPHC_FY20_EOL_COMBINED_V2 created, with 130170 rows and 35 columns.

Re: Correcting a merge dataset

Kurt_Bremser — Tue, 04 Aug 2020 15:52:50 GMT

So that simply means your large table consists mostly of member_id's that have a match in the small table.