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