replacing proc freq with sql count or similar

[pinkyc]

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

[Reeza]

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.

[pinkyc]

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!

[Reeza]

Post an example of your data and your output then.

[stat_sas · Accepted solution]

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;

[pinkyc]

Hi,

Thanks!  stat interpreted my question correctly, I'll try to post input and output data for any more questions in the future.