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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.