Using Formats to Group values in the Filter & Query Task

DaveShea — Wed, 21 Oct 2009 09:05:02 GMT

I am using SAS Enterprise Guide 4.1 (4.1.0.471) and want to be able to use the Filter & Query task to summarise my data using an existing user-defined format to group my values.

As an example, my demo data looks like this:

Data Transactions;
Input Cust_No
Trans_Code
;
Datalines;
555 1
555 1
330 2
630 2
777 2
805 3
658 4
;
Run;

My demo format looks like this:

Proc Format Library=WORK;
Value Trans_Code 1,2 = "Purchases"
3,4,5,6,7,9 = "Cash Advances"
Other = "Unknown"
;
Run;

when hand-coding (ie writing SAS code myself) I can use either of the following techniques:

1) Use Proc Summary with a Format statement to group by the Trans_Code variable after being passed through the format Trans_Code.

Proc Summary Data=Transactions NWAY missing;
Class Trans_Code;
Format Trans_Code Trans_Code.;
Output Out=SummaryA(Drop=_TYPE_ Rename=(_FREQ_=NumberOfRows))
;
Run;

2) Use Proc SQL with a Put function to group Trans_Code values by the format, Trans_Code.

Proc SQL;
Create Table SummaryB As
Select Put(Trans_Code, Trans_Code.) As GroupTranscode,
Count(*) As NumberOfRows
From Transactions
Group By Calculated GroupTranscode
;
Quit;

However, I need to be able to do this same grouping from the Filter & Query task , ie without writing code. I realise that the Recode A Column feature of the Calculated Column option can produce the same results as I require, but having to hand-build the rules for re-coding a column each time is not practical. In my real data, there are many hundreds of discreet Trans_Code values and the SAS format that we have built reduces these down to about 50 groups.

Does anyone know how we can use the Filter & Query Task to do the above. The only way I can think of doing it is to build a Calculated Column with the Expression Builder and build an expression as:

Put(Trans_Code, Trans_Code.)

I wonder if I am missing something a bit more obvious that is buried in the Filter & Query Task somwhere.

Any thoughts, anyone ?

Many thanks,

Down-under-Dave
Wellington
New Zealand

Re: Using Formats to Group values in the Filter & Query Task

RichardH_sas — Wed, 21 Oct 2009 17:54:59 GMT

My suggestion would be exactly what you mention: create a new column using a PUT function and your user-defined format. IMO, I don't think you're missing anything simpler.

Re: Using Formats to Group values in the Filter & Query Task

DerekAdams — Wed, 21 Oct 2009 18:30:56 GMT

Hi Dave

using your sample data to create Transactions and the trans_code format, I then used the filter query task and added the two columns and grouped by Cust_no.

I just changed the format of Cust_no to trans_code and came up with the same answer without using put statements.

PROC SQL;
CREATE TABLE WORK.Query_for_SUMMARYA AS SELECT SUMMARYA.Trans_Code FORMAT=TRANS_CODE.,
(SUM(SUMMARYA.NumberOfRows)) AS SUM_OF_NumberOfRows
FROM WORK.SUMMARYA AS SUMMARYA
GROUP BY SUMMARYA.Trans_Code;
QUIT;

Derek Message was edited by: Derek Adams

topic Re: Using Formats to Group values in the Filter & Query Task in SAS Enterprise Guide

Using Formats to Group values in the Filter & Query Task

Re: Using Formats to Group values in the Filter & Query Task

Re: Using Formats to Group values in the Filter & Query Task