SQL To SQL Conversion-CodePudding

I need to convert SAS to SQL; Right now, I am trying to convert the below SAS code to SQL and for me it’s the first time to convert the SAS script.

Please could anybody explain what the following does, or if possible, the equivalent in SQL?

PROC SQL;
CONNECT TO DB2(SSID=DB0P);
CREATE TABLE dclhcl AS SELECT *
FROM CONNECTION TO DB2
(SELECT
HCd_SITE_ID,
HCd_AUDNBR,
0,
HCd_PROV1,
HCd_memgrp,
HCd_from_YMD,
HCd_DTPD_YMD,
HCd_AMT_PAID,
HCd_DSLW_CDE_OTH,
HCd_REVENUE_CDE,
HCd_AMT_CLAIMED,
HCd_AMT_DSLW_OTH,
HCD_AMT_COPAY,
HCD_AMT_DEDUCT,
HCD_AMT_CONTRACT,
  hCd_RECID,
  'HOS',
  HCd_ENTRY_YMD,
  HCd_ADJ_SEQ_NBR,
  HCD_DTL_DENY_FLG,
  HCD_PRV_PAR_CDE,
hCM_CLOSE_FLG,
hCm_FED_TAX_ID,
HCm_RCV_YMD,
hcm_diagnosis_cd1,
HCm_DENIAL_CDE,
  HCm_RVW_RSN_CDE1,
  HCm_RVW_RSN_CDE2,
  HCm_RVW_RSN_CDE3,
  HCm_RVW_RSN_CDE4,
  HCm_RVW_RSN_CDE5,
  HCm_RVW_RSN_CDE6,
  HCm_RVW_RSN_CDE7,
  HCm_RVW_RSN_CDE8,
  HCm_RVW_RSN_CDE9,
  HCm_RVW_RSN_CDE0,
  hcm_PROCESS_CDE,
  HCM_TYPE_OF_BILL,
  HCM_REOPEN_FLG,
  HCM_CLAIM_TYPE,
  hcm_sec_source,
  HCM_TOT_CLAIMED,
  HCM_TOT_DSLW_OTH

FROM QDSPR.uhcHLdetl,
     QDSPR.uhchlmast
  WHERE
         hcm_site_id=hcd_site_id
     and hcm_audnbr=hcd_audnbr
     and hcm_recid=hcd_recid
     and HCM_ADJ_SEQ_NBR=HCD_ADJ_SEQ_NBR

      and (hcm_dtpd_ymd between '2020-02-16' and '2020-02-22')
      AND hcm_PROCESS_CDE in(3,4,5,6,7)
      and hcm_FIN_PRD_NBR not in(413,666,894,949,1086,1089,1094,1418,526,527,654,655);
with ur
)
AS EXTRACT(
HMOID,
AUDNBR,
AUDsub,
PROVIDER,
memgrp,
FROM_YMD,
PAID_YMD,
AMT_PAID,
rsn_CDE,
revenue,
AMT_CLAI,
AMT_DISA,
AMT_COPA,
AMT_DEDU,
AMT_CONT,
recid,
type,
entr_ymd,
sys_seq,
den_flg,
par_cde,
clos_flg,
FEDTAXID,
recv_YMD,
diag_CDE,
DEN_CDE,
rvw1,
rvw2,
rvw3,
rvw4,
rvw5,
rvw6,
rvw7,
rvw8,
rvw9,
rvw10,
process,
bill_typ,
reopen,
cov_typ,
src,
TOT_CLAI,
TOT_DISA
);
%put &sqlxmsg;


 * format ;
 * check for partial denials;
 data dens; set dclhcl;
 if tot_clai ¬= tot_disa and amt_clai = amt_disa and amt_clai ¬= 0
    and rsn_cde > 0 and rsnflg="D" then do;
    outcome="PARTIAL DENY";
    output;
 end;
 run;
 data dens;
 keep hmoid audnbr audsub recid sys_seq outcome;
 set dens;
 run;
 proc sort data=dens nodupkey;
 by hmoid audnbr audsub recid sys_seq outcome;
 run;
 proc sort data=dclhcl;
 by hmoid audnbr audsub recid sys_seq;
 run;
 data dclhcl;
 merge dens (in=ind) dclhcl (in=inh);
 by hmoid audnbr audsub recid sys_seq;
 if inh;
 run;

My conversion of the summary block:

 CREATE TABLE dclhcl
(
  HMOID,AUDNBR,AUDsub,PROVIDER,memgrp,FROM_YMD,PAID_YMD,AMT_PAID,
................
TOT_CLAI,
TOT_DISA
)

From this condition onwards if tot_clai ¬= tot_disa and amt_clai = amt_disa and amt_clai ¬= 0 and rsn_cde > 0 and rsnflg="D" then do; outcome="PARTIAL DENY"; I am unable to convert the SAS to SQL.

CodePudding user response：

This data step:

data dens;
  set dclhcl;
  if tot_clai ¬= tot_disa
   and amt_clai = amt_disa
   and amt_clai ¬= 0
   and rsn_cde > 0 
   and rsnflg="D" 
  then do;
    outcome="PARTIAL DENY";
    output;
  end;
run;

Is just making a copy of the observations in dclhcl that meet the condition. If OUTCOME is not part of original dataset then it is just the same as:

create table dens as
  select *,'PARTIAL DENY' as outcome
    from dclhcl
    where ...
;

The goofy ¬ character in your code is the EBCDIC NOT symbol. (Is your original SAS program running on an IBM mainframe). So the combination ¬= means not equal. So the subsetting condition is just:

       (not (tot_clai = tot_disa))
   and amt_clai = amt_disa
   and (not (amt_clai = 0))
   and rsn_cde > 0 
   and rsnflg='D'