Let's say I have a dataset like this:
If I use proc freq I'll get the respective frequencies of the total variable but I was wondering how to do it using sql. I used the sum function for the Admit column, but was wondering how to do something similar for Code and Patient Type?
proc sql; select
sum(Admit) as AdmitTot
from pat_data;
quit;
PatientType | Code | Admit |
---|---|---|
Sick | 999 | 1 |
Well | 123 | 1 |
Well | 999 | 0 |
If I use this for example:
proc sql;
select distinct PatientType, Code, Admit, count (*) as n
from pat_data
group by PatientType, Code, Admit;
run;
I get a table that looks like this:
PatientType | Code | Admit | n |
---|---|---|---|
Sick | 999 | 1 | 1 |
Well | 123 | 1 | 1 |
As Reeza suggested input data and output will help to understand this in a better way. This is what I understood based on your description.
proc sql;
select PatientType, count(PatientType) as freq_PatientType,count(PatientType)/(select count(*) from have) as percent format=percent7.,
count(code) as freq_code, count(Code)/(select count(*) from have) as percent format=percent7.,
count(Admit) as freq_admit, count(Admit)/(select count(*) from have) as percent format=percent7.
from have
group by PatientType;
quit;
Your code doesn't match your sample output so I don't want to assume the structure of the data
Proc freq with sparse option is easier, or possible proc tabulate or even proc means.
First create a table of all possible combinations and then merge back with the original data is the SQL method.
proc sql;
create table all_poss as
select a.patient_type, b.code
from (select distinct patient_type from have) as a
CROSS JOIN
(select distinct code from have) as b;
quit;
*Then join/merge with desired table above.
Sorry I think I phrased the question badly. The first table is just a dataset with some info., if I use proc freq I'll get that PatientType had 67% well, 33% sick. I was trying to use the count (*) in sql to achieve the same result but instead, it just counts something else and gives a n = 1 as an extra column so I don't think I'm using it correctly. Is there a way to use sql to count each column and report the frequency exactly like proc freq?
So it would report PatientType having 67% well, 33%. Code, 67% 999, and 33% 123. For Admit, I was using Sum which works but I was wondering if there's a way using count to do it for all three columns.
Thanks!
Post an example of your data and your output then.
As Reeza suggested input data and output will help to understand this in a better way. This is what I understood based on your description.
proc sql;
select PatientType, count(PatientType) as freq_PatientType,count(PatientType)/(select count(*) from have) as percent format=percent7.,
count(code) as freq_code, count(Code)/(select count(*) from have) as percent format=percent7.,
count(Admit) as freq_admit, count(Admit)/(select count(*) from have) as percent format=percent7.
from have
group by PatientType;
quit;
Hi,
Thanks! stat interpreted my question correctly, I'll try to post input and output data for any more questions in the future.
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!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.