Help using Base SAS procedures

replacing proc freq with sql count or similar

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

replacing proc freq with sql count or similar

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;

PatientTypeCodeAdmit

Sick

9991
Well1231
Well9990

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:

PatientTypeCodeAdmitn
Sick99911
Well12311

Accepted Solutions
Solution
‎01-06-2015 12:30 PM
Trusted Advisor
Posts: 1,204

Re: replacing proc freq with sql count or similar

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;

View solution in original post


All Replies
Super User
Posts: 17,899

Re: replacing proc freq with sql count or similar

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.

Contributor
Posts: 28

Re: replacing proc freq with sql count or similar

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!

Super User
Posts: 17,899

Re: replacing proc freq with sql count or similar

Post an example of your data and your output then.

Solution
‎01-06-2015 12:30 PM
Trusted Advisor
Posts: 1,204

Re: replacing proc freq with sql count or similar

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;

Contributor
Posts: 28

Re: replacing proc freq with sql count or similar

Hi,

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 3328 views
  • 3 likes
  • 3 in conversation