topic Re: how to use proc sql to count numbers and calculate percentage in SAS Programming

how to use proc sql to count numbers and calculate percentage

Bal23 — Mon, 10 Apr 2017 18:34:34 GMT

proc sql;
create table new as select distinct 
patientid
,(case when group = "-1" then 1 else 0 end) as group_ind
from lib1.file2
;
quit;


proc sql;

create table want  as

select 


        count(distinct patientid) as total,

    count(distinct group_ind)>0))as id_ct_r,

       
calculated id_ct_r / total as percent_group format percent7.2
from new;
quit;

i need to calculate the percentage. I can do with datastep but would like to try proc sql. Above one has an error message. Can anyone give advice or provide a better way to calculate the rate, a percentage, how many with the value "-1" for the variable group (it is a character variable, and the value "-1" has a special meaning. All other values would be a eight-number value.

Thank you.

Re: how to use proc sql to count numbers and calculate percentage

art297 — Mon, 10 Apr 2017 18:56:44 GMT

Show example have and want data sets in the form of a datastep. Your first call to proc sql reduces your data to one record for each patientid recoded group combination .. which may not be what you want to start with.

Art, CEO, AnalystFinder.com

Re: how to use proc sql to count numbers and calculate percentage

HB — Mon, 10 Apr 2017 21:06:36 GMT

You would think SQL like

select

sum(field1) as sum1,

count(field1) as count1,

sum1/count1 as myaverage

from

someplace

would work, but it never does.

You always end up repeating the expressions and not being able to use the aliases. Which is annoying.

select

sum(field1) as sum1,

count(field1) as count1,

sum(field1)/count(field1) as myaverage

from wherever

Yes, I know there is an AVG function.

Re: how to use proc sql to count numbers and calculate percentage

Tom — Mon, 10 Apr 2017 21:18:56 GMT

It always helps to show some example data.

So if you had this data:

data have ;
  input group $ patientid $ @@;
cards;
1 1 1 2 1 3 
-1 1 -1 2
2 4 2 5
;

But it sounds you want something like

select count(distinct patientid) as N_patients
     , count(distinct case when group='-1' then patientid end) as N_minus_one
     , calculated N_minus_one / calculated N_patients as Percent_minus_one
  from have
;

Then you would get 5,2 and 0.4 as the answers since there are 5 distinct PATIENTID values of which only 2 are in the '-1' group.