count in proc sql

[kevin]

Hello,

I have a dataset as following;

Level id var1 var2
1 100 1 1
1 200 0 1
1 300 1 1
1 400 1 1
2 100 0 1
2 200 0 1
2 300 1 1
2 400 1 1
3 100 1 1
3 200 0 1
3 400 1 1
4 100 1 1
4 300 0 1
4 400 1 1

I'd like to use proc sql to make following table;

table cntvar
level id var1cnt var1cnt var2cnt varcnt varcntmean
1 xx
2 xx
3 xx
4 xx
1 100 xx xx xx xx
1 200 xx xx xx xx
...
4 400 xx xx xx xx



Proc sql;
Creat table cntvar as
Select level,
id,
count(var1) as var1cnt, /* here, count(var1—where var1=1)*/
count(var2) as var2cnt, /* here, count(var2--where all of var2=1)*/
count(var1)/count(var2) as varcnt
group by level, id;

select mean(varcnt)as varcntmean
group by level;

run;
quit;

Here are the questions:
1. how to do count(var1) and count(var2) in the same time
2. can I achieve this -- varcnt and varcntmean in one table. Any suggestion.

Thanks,

[Jan Selchau-Hansen]

Post by kevin
Hello,
I have a dataset as following;
Level id var1 var2
1 100 1 1
1 200 0 1
1 300 1 1
1 400 1 1
2 100 0 1
2 200 0 1
2 300 1 1
2 400 1 1
3 100 1 1
3 200 0 1
3 400 1 1
4 100 1 1
4 300 0 1
4 400 1 1
I'd like to use proc sql to make following table;
table cntvar
level id var1cnt var1cnt var2cnt varcnt varcntmean
1 xx
2 xx
3 xx
4 xx
1 100 xx xx xx xx
1 200 xx xx xx xx
...
4 400 xx xx xx xx
Proc sql;
Creat table cntvar as
Select level,
id,
count(var1) as var1cnt, /* here, count(var1-where var1=1)*/
count(var2) as var2cnt, /* here, count(var2--where all of var2=1)*/
count(var1)/count(var2) as varcnt
group by level, id;
select mean(varcnt)as varcntmean
group by level;
run;
quit;
1. how to do count(var1) and count(var2) in the same time
2. can I achieve this -- varcnt and varcntmean in one table. Any suggestion.
Thanks,

It looks like a job for Proc Summary !

Jan

[Richard A. DeVenezia]

Post by kevin
Hello,
I have a dataset as following;

Your sample data has no case where the group by level, id has more than one
row.
In general SAS SQL does not have a simple way to self union a subquery to
stack data in various group by forms. Multiple views are clearer, and more
likely a different proc better suites the problem; and finally, redefining
the problem can also remove clutter that would otherwise appear.

* if suppositions are wrong, then perhaps that is all that is wrong ;

data homework;
input Level id var1 var2;
cards;
1 100 1 1
1 200 0 1
1 300 1 1
1 400 1 1
2 100 0 1
2 200 0 1
2 300 1 1
2 400 1 1
3 100 1 1
3 200 0 1
3 400 1 1
4 100 1 1
4 300 0 1
4 400 1 1
run;

* some mean is replicated into each row of a group, I found this easier than
stacking the various summary forms using outer union corresponding;

proc sql;
create view counts as
select *
, mean (actualizedRatio) as someMean
, sum (actualizedRatio) as someMeanNumerator
, count (*) as someMeanDenominator
from
(
select level
, id
, count(*) as nRowsInGroup
, sum (var1 ne 0 and var1 > .) as nNeitherZeroNorMissingVar1
, sum (var2 ne 0 and var2 > .) as nNeitherZeroNorMissingVar2
, calculated nNeitherZeroNorMissingVar1
/ calculated nNeitherZeroNorMissingVar2
as actualizedRatio
from homework
group by id,level
)
group by level
;
quit;

[Chang Y. Chung]

Post by kevin
Hello,
I have a dataset as following;
Level id var1 var2
1 100 1 1
1 200 0 1
1 300 1 1
1 400 1 1
2 100 0 1
2 200 0 1
2 300 1 1
2 400 1 1
3 100 1 1
3 200 0 1
3 400 1 1
4 100 1 1
4 300 0 1
4 400 1 1
I'd like to use proc sql to make following table;
table cntvar
level id var1cnt var1cnt var2cnt varcnt varcntmean
1 xx
2 xx
3 xx
4 xx
1 100 xx xx xx xx
1 200 xx xx xx xx
...
4 400 xx xx xx xx
Proc sql;
Creat table cntvar as
Select level,
id,
count(var1) as var1cnt, /* here, count(var1—where var1=1)*/
count(var2) as var2cnt, /* here, count(var2--where all of

var2=1)*/

Post by kevin
count(var1)/count(var2) as varcnt
group by level, id;
select mean(varcnt)as varcntmean
group by level;
run;
quit;
1. how to do count(var1) and count(var2) in the same time
2. can I achieve this -- varcnt and varcntmean in one table. Any

suggestion.

Hi, Kevin,

A1: count() takes an expression instead of column name. and boolean TRUE
can be evaluated as 1. so count(var1=1) will return the number of records
with var1=1.

A2: you can concatenate tables using "outer union"ing the queries.

Here is the whole thing -- one way among many. HTH.

Cheers,
Chang

data one;
input level id var1 var2;
cards;
1 100 1 1
1 200 0 1
1 300 1 1
1 400 1 1
2 100 0 1
2 200 0 1
2 300 1 1
2 400 1 1
3 100 1 1
3 200 0 1
3 400 1 1
4 100 1 1
4 300 0 1
4 400 1 1
;
run;

proc sql;
create view Levels as
select distinct Level
from one
order by Level
;
create view LevelId as
select level, id
from levels, (select distinct id from one)
order by level, id
;
create view counts as
select Level
, id
, count(var1=1) as var1cnt
, count(var2=1) as var2cnt
, calculated var1cnt / calculated var2cnt as varcnt
from one
group by Level, id
order by Level, id
;
select L.Level
, . as id
, . as var1cnt
, . as var2cnt
, . as varcnt
, mean(varcnt) as varcntmean
from Levels as L left join counts as c
on L.Level = c.Level
group by L.Level

outer union corresponding

select Li.Level
, Li.Id
, coalesce(c.var1cnt,0) as var1cnt
, coalesce(c.var2cnt,0) as var2cnt
, coalesce(c.varcnt, 0) as varcnt
from LevelId as Li left join counts as c
on Li.Level = c.Level and Li.id = c.id
;
quit;
/* on lst
level id var1cnt var2cnt varcnt varcntmean
------------------------------------------------------------
1 . . . . 1
2 . . . . 1
3 . . . . 1
4 . . . . 1
1 100 1 1 1 .
1 200 1 1 1 .
1 300 1 1 1 .
1 400 1 1 1 .
2 100 1 1 1 .
2 200 1 1 1 .
2 300 1 1 1 .
2 400 1 1 1 .
3 100 1 1 1 .
3 200 1 1 1 .
3 300 0 0 0 .
3 400 1 1 1 .
4 100 1 1 1 .
4 200 0 0 0 .
4 300 1 1 1 .
4 400 1 1 1 .
*/