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
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).
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 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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.