topic SAS Conversion to SQL in SAS Programming

SAS Conversion to SQL

venu5876 — Mon, 05 Sep 2022 10:47:53 GMT

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_fed_tax_id &tins)
and hcm_site_id &divs
and hcm_FIN_PRD_NBR not &fp
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;

* 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
)

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";

From the above step if tot_clai ¬= tot_disa and amt_clai = amt_disa and amt_clai ¬= 0 onwards I am unable to convert

Re: SAS Conversion to SQL

PaigeMiller — Mon, 05 Sep 2022 10:56:40 GMT

Taking a step back ... why convert to SQL if you have working SAS code? Seems like an unnecessary thing to do.

Re: SAS Conversion to SQL

venu5876 — Mon, 05 Sep 2022 11:07:41 GMT

We are using SQL Server as a backend database for one product. For that product this SAS script data is required. So I am developing the Stored Procedure with the above SAS Conditions in SQL...
That is the reason I am trying to convert the below condition in SQL. Could you please suggest me on this!!

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";

Re: SAS Conversion to SQL

PaigeMiller — Mon, 05 Sep 2022 11:20:24 GMT

@venu5876 wrote:

Could you please suggest me on this!!

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";

The equivalent of a SAS IF statement in SQL is the CASE WHEN statement.

Re: SAS Conversion to SQL

LinusH — Mon, 05 Sep 2022 12:36:32 GMT

Just to set the expectations - this is a site for persons that wants to learn (and teach) SAS, not for decommission SAS solutions.

A bit harsh, but if your project need SAS knowledge, pay for it.

Re: SAS Conversion to SQL

venu5876 — Mon, 05 Sep 2022 12:49:29 GMT

Thanks for your support
I have used the CASE Statement as suggested. It worked

Re: SAS Conversion to SQL

venu5876 — Mon, 05 Sep 2022 12:50:54 GMT

As you suggested I will use this portal as a SAS learning Hub
Thanks for your time and support.