## Count Variable States Across Multiple Columns using Proc Sql

Solved
Occasional Contributor
Posts: 10

# Count Variable States Across Multiple Columns using Proc Sql

Hi,

I am looking for a proc sql approach to summarize a set of variables that have only 3 states of Valid, Missing, or OOR (out of range) values and represented by 0, 1, or 2, respectively.

In the data set below, there are two groups with four variables that have 3 states (0,1, or 2).

data have;
length grp \$1 v1 v2 v3 v4 4;
input grp v1 v2 v3 v4 ;
datalines;
1 0 0 0 1
1 0 0 0 1
1 0 1 0 1
1 0 1 0 1
2 1 1 0 2
2 1 1 0 2
2 1 2 2 2
2 1 2 2 2
;
run;

What proc sql command (or other, if needed) can generate the following output, which summarizes the count of each state by group by variable.

data want;
length grp \$1 var \$4 type0 type1 type2 4;
input grp var type0 type1 type2;
datalines;
1 v1 4 0 0
1 v2 2 2 0
1 v3 4 0 0
1 v4 0 4 0
2 v1 0 4 0
2 v2 0 2 2
2 v3 2 0 2
2 v4 0 0 4
;
run;

rg

Accepted Solutions
Solution
‎11-30-2017 12:58 PM
Super User
Posts: 13,283

## Re: Count Variable States Across Multiple Columns using Proc Sql

Here's an example that works for two variables, at least for your example data.

You would have to union each variable

```proc sql;
create table want as

select grp, 'V1' as var,sum(v1=0) as type0,sum(v1=1) as type1,sum(v1=2) as type2
from have
group by grp
union
select grp, 'V2' as var,sum(v2=0) as type0,sum(v2=1) as type1,sum(v2=2) as type2
from have
group by grp

;
quit;```

I really wouldn't relish doing this type of operation for many variables or levels of variables.

I am not going to make any claim to efficiency.

All Replies
Super User
Posts: 23,224

## Re: Count Variable States Across Multiple Columns using Proc Sql

Can you use PROC FREQ? You can do it in SQL but it's definitely very very ugly.

Occasional Contributor
Posts: 10

## Re: Count Variable States Across Multiple Columns using Proc Sql

Reeza,

So in SAS, we have the luxury of alternative approaches with procs designed for certain tasks.  Part of the reason for this post is to help me move some POC development work into non-SAS environments.

But really, this problem is stumping me from a sql perspective ... and ugly would make me not feel so bad for not seeing the solution

rg

Super User
Posts: 23,224

## Re: Count Variable States Across Multiple Columns using Proc Sql

Part of the reason for this post is to help me move some POC development work into non-SAS environments.

I would probably visit the forum of those environments then instead of SAS. Replicating code, line by line/task by task is not efficient. Rather, determine processes and implement in new tools from scratch so that's its more efficient. You make less mistakes, it's overall faster and the results are better with this type of approach.

The SQL approach we provide here may not be appropriate for your new environment. The new env may have windowing or partitioning functions that make this easier than using BASE SQL.

Occasional Contributor
Posts: 10

## Re: Count Variable States Across Multiple Columns using Proc Sql

I will keep that in mind, thank you.

Solution
‎11-30-2017 12:58 PM
Super User
Posts: 13,283

## Re: Count Variable States Across Multiple Columns using Proc Sql

Here's an example that works for two variables, at least for your example data.

You would have to union each variable

```proc sql;
create table want as

select grp, 'V1' as var,sum(v1=0) as type0,sum(v1=1) as type1,sum(v1=2) as type2
from have
group by grp
union
select grp, 'V2' as var,sum(v2=0) as type0,sum(v2=1) as type1,sum(v2=2) as type2
from have
group by grp

;
quit;```

I really wouldn't relish doing this type of operation for many variables or levels of variables.

I am not going to make any claim to efficiency.

Occasional Contributor
Posts: 10

## Re: Count Variable States Across Multiple Columns using Proc Sql

Ballardw

This works!

Fortunately, the grouping variable is the lowest dimension, so another join to a dim table will allow other aggregations for reporting. To your other comment, the structure of the command is conducive to automated code generation for an arbitrary array of variables, which could be executed in a variety of data frames.

Thank you for taking time to look at this ... you moved me along on my project!

rg

☑ This topic is solved.