topic replacing proc freq with sql count or similar in SAS Procedures

replacing proc freq with sql count or similar

pinkyc — Tue, 06 Jan 2015 16:53:05 GMT

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

Re: replacing proc freq with sql count or similar

Reeza — Tue, 06 Jan 2015 17:04:44 GMT

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.

Re: replacing proc freq with sql count or similar

pinkyc — Tue, 06 Jan 2015 17:13:25 GMT

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!

Re: replacing proc freq with sql count or similar

Reeza — Tue, 06 Jan 2015 17:17:08 GMT

Post an example of your data and your output then.

Re: replacing proc freq with sql count or similar

stat_sas — Tue, 06 Jan 2015 17:30:18 GMT

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;

Re: replacing proc freq with sql count or similar

pinkyc — Tue, 06 Jan 2015 18:10:23 GMT

Hi,

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