topic sql merge in SAS Programming

sql merge

Ranjeeta — Fri, 06 Sep 2019 15:32:27 GMT

Hello,

I am getting 0 rows as output with no errors

i think it is to do with the last condition i added re inst_no both are in character format

do i need to put them as numeric?

proc sql;
227 create table EVTwNACRS as
228 select a.CIHI_key as CIHI_KEY_NAC,a.reg_dt, a.Reg_tm,a.Facility_NO as Facility_NAC, a.INST_NO as INST_AM,
229 a.ARRIVAL_DT, a.ARRIVAL_TM, b.CIHI_KEY as CIHI_KEY_DAD,a.FLAG_SP340_NACRS,a.sp340NACRS, b.Fiscal_QTR,b.EVT_INST_AMnew, b.EVT_INST_ATnew,
229! b.EVT_INST_NAMEnew,b.ADM_DT, b.ADM_TM, b.SP440,b.SP340,b.FLAG_SP440,b.flag_SP340, b.EVT_TM,
230 b.Facility_NO as Facility_DAD , b.HCNE as HCN_SP440,b.diag_code_01,b.diag_code_02,b.diag_code_03,b.diag_code_04,b.diag_code_05,b.diag_code_06,b.diag_code_07,
231 b.diag_code_08,b.diag_code_09,b.diag_code_10,b.diag_code_11,b.diag_code_12,b.diag_code_13,b.diag_code_14,b.diag_code_15,b.diag_code_16,b.diag_code_17,b.diag_code_1
231! 8,b.diag_code_19,b.diag_code_20,b.diag_code_21,b.diag_code_22,b.diag_code_23,b.diag_code_24,b.diag_code_25,b.diag_type_01,b.diag_type_02,b.diag_type_03,b.diag_type
231! _04,
232 b.diag_type_05,b.diag_type_06,b.diag_type_07,b.diag_type_08,b.diag_type_09,b.diag_type_10,b.diag_type_11,b.diag_type_12,b.diag_type_13,b.diag_type_14,b.diag_type_1
232! 5,b.diag_type_16,b.diag_type_17,b.diag_type_18,b.diag_type_19,b.diag_type_20,b.diag_type_21,b.diag_type_22,b.diag_type_23,b.diag_type_24,b.diag_type_25
233 from NAC_FY1718_emerg as a inner join EVTwInstn as b
234 on a.HCNE=b.HCNE
235 and 0<=datepart(b.ADM_DT)-datepart(a.Reg_DT)<=1 and
236 a.INST_NO=b.EVT_INST_AMnew
237 ;
NOTE: Table WORK.EVTWNACRS created, with 0 rows and 73 columns.

238 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

Re: sql merge

ballardw — Fri, 06 Sep 2019 16:27:16 GMT

@Ranjeeta wrote:

Hello,

I am getting 0 rows as output with no errors

i think it is to do with the last condition i added re inst_no both are in character format

do i need to put them as numeric?

Are you referring to this part of your code?

 a.INST_NO=b.EVT_INST_AMne

If both values are character then a common issue for not matching is capitalization. "Bob" is not equat to "bob". If that might be the case with your data then perhaps this may help.

upcase( a.INST_NO) = upcase(b.EVT_INST_AMne)

Another potential issue is leading spaces: " bob" is not equal to "bob". The STRIP function can be used to remove such spaces in the comparison

strip (upcase( a.INST_NO)) = strip(upcase(b.EVT_INST_AMne))

Other comparisons than = would be needed if you are looking to match part of one character value with another.

But we don't have any of your data to say specifically why you get no results.

Re: sql merge

PGStats — Fri, 06 Sep 2019 19:43:46 GMT

What kind of numbers are a.INST_NO and b.EVT_INST_AMnew? If integers, then the problem is probably elsewhere. But if real numbers, they might need rounding to match.