Proc SQL - Force Missing Data so Left Join will work?

shounster — Wed, 03 Oct 2018 17:34:17 GMT

I'm not 100% sure if this exact issue has been solved, and I apologize before hand if it has. Sometimes finding the right syntax to ask the community eludes me.

Anyway, there's a lot going on here. In a nut shell, I am trying to merge similar tables together based on slightly different criteria when building the pre-merge tables. I would like one table (left) to have every instance of an age grouping so when I merge the right table, it doesn't loose records because there wasn't a match.

First table (Right table) is created with the following code:

proc sql;
create table hosp_in as
select MS_DRG_Code
,Service_Line_Group
,Service_Line
,Sub_Service_Line
,Payor_Group
,DISCHARGE_QTRYR
,Service_Area
,State_Hospital_ID
,Hospital_Name
,age_group_detail
,sum(Market_In_Migration) as Market_In_Migration
,sum(Client_In_Market) as Client_In_Market
,sum(Client_In_Migration) as Client_In_Migration
from hosp_inx
group by MS_DRG_Code
,Service_Line_Group
,Service_Line
,Sub_Service_Line
,Payor_Group
,DISCHARGE_QTRYR
,Service_Area
,State_Hospital_ID
,Hospital_Name
,age_group_detail
having service_Area ^= '' ;
quit;

This table seems to create correctly and I have been able to match the results using proc summary.

This is the code for the second table (Left Table):

PROC SQL;
CREATE TABLE MIG_FINALx AS
SELECT DISTINCT
t1.DISCHARGE_QTRYR
,t1.MS_DRG_Code
,t1.MS_DRG_Desc
,t1.Service_Line_Group
,t1.Service_Line
,t1.Sub_Service_Line
,t1.SA as Service_Area
,t1.Payor_Group
,t1.state_hospital_id
,t1.hospital_name
,t1.age_group_detail
,(MAX(t1.Contribution_Margin)) FORMAT=dollar16.2 AS Contribution_Margin
,(SUM(t1.SA_pt)) AS Market_Total
,(sum(case when SA_pt=1 and compress(upcase(t1.hospital_SA)) ^= compress(upcase(scan(t1.Service_Area,1," ")))
then 1 else 0 end)) AS Market_Out_Migration
FROM MIG2 t1
GROUP BY t1.MS_DRG_Code
,t1.Service_Line_Group
,t1.Service_Line
,t1.Sub_Service_Line
,t1.Payor_Group
,t1.DISCHARGE_QTRYR
,t1.SA
,t1.state_hospital_id
,t1.hospital_name
,t1.age_group_detail
HAVING Service_Area ^= ''
;
QUIT;

The second table is doing what it is 'told'; however, the age_group_detail is what is causing problems in the final merge:

proc sql;
create table mig_final as
select t1.DISCHARGE_QTRYR
,t1.MS_DRG_Code
,t1.MS_DRG_Desc
,t1.Service_Line_Group
,t1.Service_Line
,t1.Sub_Service_Line
,t1.Service_Area
,t1.Payor_Group
,t1.Contribution_Margin
,t1.Market_Total
,t1.Market_Out_Migration
,t2.Market_In_Migration
,t2.Client_In_Market
,t2.Client_In_Migration
,t1.State_Hospital_ID
,t1.Hospital_Name
,t1.age_group_detail
from mig_finalx t1
LEFT JOIN HOSP_IN t2
on compress(upcase(scan(t1.Service_Area,1," "))) =
compress(upcase(t2.Service_Area))
and compress(t1.Discharge_QtrYr)=compress(t2.Discharge_QtrYr)
and compress(t1.MS_DRG_Code) = compress(t2.MS_DRG_Code)
and compress(t1.Service_Line_Group) =
compress(t2.Service_Line_Group)
and compress(t1.Service_Line) = compress(t2.Service_Line)
and compress(t1.Sub_Service_Line) = compress(t2.Sub_Service_Line)
and compress(t1.Payor_Group) = compress(t2.Payor_Group)
and compress(t1.state_hospital_id) =
compress(t2.state_hospital_id)
and t1.age_group_detail = t2.age_group_detail
;

quit;

When I create the mig_final table (above), where the age_group_detail isn't 'complete' or missing like the first table, it omits data from the first/right (hosp_in) table. I understand that the left join will keep the records from the left table (mig_finalx). However, a straight join doesn't seem to work either. I know that proc sql handles missing values differently than data steps or procedures in SAS, but I just can't seem to find the right combination.

On this same tangent, I would like to create a proc summary from the sql that creates mig_finalx, but I'm not sure I understand how I can 'convert' the case statement unless I do a data step to create the out_migration variable and then run proc summary.

I might be able to attach data if necessary. Any assistance would be greatly appreciated.

Re: Proc SQL - Force Missing Data so Left Join will work?

ChrisNZ — Wed, 03 Oct 2018 22:51:10 GMT

You are not being reasonable here.

Your queries are long and you provide no data.

Please spend the time to create a few lines of mock data illustrating your issue and post it, together with expected output.

Re: Proc SQL - Force Missing Data so Left Join will work?

shounster — Thu, 04 Oct 2018 16:14:19 GMT

Apologies for not providing enough information. My initial hope was that there was just something obvious in the code that I was missing and that data would not be necessary. Unfortunately, there isn't a way to just provide a few lines of data for testing and I debated on whether or not to post this because of that dilemma.

Anyway, apologies again for those of you that took time away to view any of this. Please consider this post withdrawn and no replies are necessary.

topic Re: Proc SQL - Force Missing Data so Left Join will work? in SAS Programming

Proc SQL - Force Missing Data so Left Join will work?

Re: Proc SQL - Force Missing Data so Left Join will work?

Re: Proc SQL - Force Missing Data so Left Join will work?