Count Variable States Across Multiple Columns using Proc Sql

[Richard_Gordon]

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;

Thanking you in advance,

rg

[Reeza]

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

[Richard_Gordon]

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

[Reeza]

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. 

[Richard_Gordon]

I will keep that in mind, thank you.

[ballardw · Accepted solution]

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.

[Richard_Gordon]

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