Proc Format Library=WORK;
Value Trans_Code 1,2 = "Purchases"
3,4,5,6,7,9 = "Cash Advances"
Other = "Unknown"
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;
Format Trans_Code Trans_Code.;
Output Out=SummaryA(Drop=_TYPE_ Rename=(_FREQ_=NumberOfRows))
2) Use Proc SQL with a Put function to group Trans_Code values by the format, Trans_Code.
Create Table SummaryB As
Select Put(Trans_Code, Trans_Code.) As GroupTranscode,
Count(*) As NumberOfRows
Group By Calculated GroupTranscode
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:
I wonder if I am missing something a bit more obvious that is buried in the Filter & Query Task somwhere.
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.
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;