Count Variable States Across Multiple Columns using Proc Sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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;

 

Thanking you in advance,

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

Posted in reply to Richard_Gordon

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.

View solution in original post


All Replies
Super User
Posts: 23,224

Re: Count Variable States Across Multiple Columns using Proc Sql

Posted in reply to Richard_Gordon

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 Smiley Happy

rg

 

 

Super User
Posts: 23,224

Re: Count Variable States Across Multiple Columns using Proc Sql

Posted in reply to Richard_Gordon

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

Posted in reply to Richard_Gordon

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 188 views
  • 0 likes
  • 3 in conversation