SQL到SQL轉換

我需要將 SAS 轉換為 SQL；現在，我正在嘗試將下面的 SAS 代碼轉換為 SQL，對我來說這是第一次轉換 SAS 腳本。

請任何人解釋以下內容，或者如果可能，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;

我對摘要塊的轉換：

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

從這種情況開始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"; ，我無法將 SAS 轉換為 SQL。

uj5u.com熱心網友回復：

此資料步驟：

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;

只是在 dclhcl 中復制符合條件的觀察結果。如果 OUTCOME 不是原始資料集的一部分，則它與以下內容相同：

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

代碼中愚蠢的 ? 字符是 EBCDIC NOT 符號。（您的原始 SAS 程式是否在 IBM 大型機上運行）。所以組合 ?= 表示不相等。所以子集化條件就是：

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

標籤：mysql sql服务器 sas

上一篇：如何在存盤程序中創建資料陣列

下一篇：計算col1中的每個不同專案多少次col2=0ANDcol3=0