topic Re: Create two range variables in order to pull a percent from a total count in New SAS User

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

EdwardHousman — Mon, 17 Jan 2022 20:36:45 GMT

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

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

PaigeMiller — Mon, 17 Jan 2022 21:22:53 GMT

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

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

EdwardHousman — Mon, 17 Jan 2022 21:52:43 GMT

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.

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

PaigeMiller — Tue, 18 Jan 2022 10:54:22 GMT

@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