turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- how to use proc sql to count numbers and calculate...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-10-2017 02:34 PM

```
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to wenling

04-10-2017 02:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to wenling

04-10-2017 05:06 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to wenling

04-10-2017 05:08 PM - edited 04-10-2017 05:18 PM

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.