SQL到SQL转换
SQL到SQL转换
提问于 2022-09-05 11:02:26
我需要将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。
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-09-05 15:34:16
这一数据步骤：
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观测结果。如果结果不是原始数据集的一部分，则与以下内容相同：
create table dens as
select *,'PARTIAL DENY' as outcome
from dclhcl
where ...
;代码中的愚蠢字符是EBCDIC，而不是符号。(是运行在IBM大型机上的原始SAS程序)。所以组合¬=的意思是不相等。因此，细分条件是：
(not (tot_clai = tot_disa))
and amt_clai = amt_disa
and (not (amt_clai = 0))
and rsn_cde > 0
and rsnflg='D' 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73608326
复制相关文章
相似问题
腾讯云开发者
