CODEWITHSUNDEEP
sqlcountsasuniquedistinct-values
Kon Hai Woh
Kon Hai Woh

Reputation: 53

SAS: Need to count number of instances per id

Let's assume I have table1:

id  value1  value2 value3
1   z       null    null
1   z       null    null
1   null    y       null
1   null    null    x
2   null    y       null
2   z       null    null 
3   null    y       null
3   null    null    null 
3   z       null    null


id  value1  value2 value3
1   z       null    null
1   z       null    null
1   null    y       null
1   null    null    x
2   null    y       null
2   z       null    null 
3   null    y       null
3   null    null    null 
3   z       null    null

and I have table2:

id  
1  
2  
3

I want to count number of values in each column per id to have output like this. (ex. id 1 has 2 - z's, one y and one x)

 id value1 value2  value3  
  1   2      1      1     
  2   1      1      0   
  3   1      1      0

Need to do this in SAS. There is an example of this in Oracle but not in SAS.

Upvotes: 0

Views: 1941

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

If I understand correctly, this is a simple query using proc sql. For all the ids in the first table:

proc sql;
    select id, count(val1) as val1, count(val2) as val2, count(val3 as val3)
    from table1
    group by id;
run;

count() counts the number of non-NULL values in a column or expression.

Upvotes: 0

Related Questions