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.
... View more
