Create two range variables in order to pull a percent from a total count

[EdwardHousman]

Hello all.  I have had a four year break from coding DB SQL, and now I am taking a course in SAS Enterprise Guide. I was given an assignment to pull a percentage from 2 range variables.  One range is for high medical codes, and the other is for low medical codes.  Below is a screen shot of the directions.

Below is a yes/no data scenario of the records that would be pulled.   

HN=Hospital_Num

COD=Proc_Code

CT= Total Claim Count for Proc_Code

Include Yes/No = my explanation of why a record would be included in the report.

Output Example

 My code is generated in EG. My code gives a count of the different codes.  What I need is to get a total of the high range codes ('14','15') and divide this by a total of all code counts grouped by hospital.  This is where I can get a percentage of the high code counts each hospital has.  I believe this might be done with compound case statements.  I used case statements to get the counts of the individual counts.  See Code below.

PROC SQL;

   CREATE TABLE DBS.t_flags AS

   SELECT DISTINCT t1.HN,

            t1.COD,

          /* sum_code */

            (SUM((CASE

               WHEN t1.COD between '14' and '15'

               THEN 1

             when t1.cod not between '14' and '15'

            then 1

            else 0

            end))) AS sum_cod

      FROM DBS.T_DETAIL t1

      GROUP BY t1.BN,

               t1.COD

      ORDER BY t1.BN,

QUIT;

Any assistance would be greatly appreciated.  I would like to become an expert in Proc SQL, and even data step. It would be great if I learn enough to help others out on this website. 

Sincerely, 

Edward

[PaigeMiller · Accepted solution]

UNTESTED CODE

proc format;
    value billf 
        11-13='Not High Billing Code'
        14-15='High Billing Code';
run;
proc freq data=have;
     weight cod_ct;
     tables hn*cod/noprint out=want;
     format cod billf.;
run;

Then you can remove observations from WANT where the high billing code is not 50% or more.

If you want tested code, please provide the data as SAS data step code (instructions) and not as screen captures, and not in other formats.

ADVICE: SAS has lots of built in functions that you should be using, rather than coding up your own version of the function in PROC SQL. PROC FREQ should be your first choice for computing percents and frequencies. Yes, SQL can do it, but PROC FREQ has been debugged by SAS and validated in thousands of real world situations; if you do it with your own custom SQL code, you have to vouch for the correctness of the results (and of course, you also have to struggle to get the proper SQL code).

[EdwardHousman]

Thanks so much Paige. Is PROC FREQ a procedure in data step? I believe this will solve the problem. I will look on this site for examples of PROC FREQ. Thanks much.

[PaigeMiller]

---

@EdwardHousman wrote:
Thanks so much Paige. Is PROC FREQ a procedure in data step?

---

PROC FREQ is a procedure. It is not in the data step. Documentation and examples: https://documentation.sas.com/doc/en/pgmmvacdc/9.4/procstat/procstat_freq_toc.htm