SAS List of variables by type Outcome with Sum Conversion in SQL

user6ryi1 0
Viewed 23

I am trying to convert the below SAS code to SQL. While converting the below SAS script I am unable to convert the “output out=affinit sum=” Step.

    ** sort
    proc sort data=dclhcl;
    by type HMOID AUDNBR AUDSUB RECID ENTR_YMD SYS_SEQ deny_cde ;
    run;
    
     ** roll up for output ;
     proc summary data = dclhcl;
     BY type HMOID AUDNBR AUDSUB RECID ENTR_YMD sys_seq deny_cde;
     var amt_clai amt_disa amt_cont amt_paid amt_copa amt_dedu amt_seq;
     id category fedtaxid from_ymd recv_ymd paid_ymd den_text
        lob status outcome cov_typ par_cde memgrp;
     output out=affinit sum=;
     run;

My conversion for the above SAS.

    SELECT amt_clai,amt_disa,amt_cont,amt_paid,amt_copa,amt_dedu,amt_seq,
     id,category,fedtaxid,from_ymd,recv_ymd,paid_ymd,den_text,
        lob,status,outcome,cov_typ,par_cde,memgrp FROM Tbl_ESCPROV_Partial_Denail_Final WITH (NOLOCK)
    ORDER BY HMOID,AUDNBR,AUDSUB,RECID,ENTR_YMD,sys_seq,deny_cde;

I am unable to convert the roll up for output step in SQL. Can anyone help me with a solution for the above SAS conversion.

db2 sas sql-server
1 Answers

You need to use GROUP BY if you want to calculate the SUM.

SELECT type
     , HMOID
     , AUDNBR
     , AUDSUB
     , RECID
     , ENTR_YMD
     , SYS_SEQ
     , deny_cde 
     , sum(amt_clai) as amt_clai
     , sum(amt_disa) as amt_disa 
     , ...
FROM Tbl_ESCPROV_Partial_Denail_Final
GROUP BY type
     , HMOID
     , AUDNBR
     , AUDSUB
     , RECID
     , ENTR_YMD
     , SYS_SEQ
     , deny_cde 
;

If you want to keep the ID variables also then add them to the list of variables in SELECT and the GROUP BY clause.

user7NPk0
Related