BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pinkyc
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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

5 REPLIES 5
Reeza
Super User

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
Calcite | Level 5

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
Super User

Post an example of your data and your output then.

stat_sas
Ammonite | Level 13

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
Calcite | Level 5

Hi,

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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