proc sql summary alternative

[aha123]

I have a proc sql steps to produce a summary data based on the same table like this:

proc sql;

  create table combined as

  select 'type 1' as type, state, sum(paid_amt) as paid_amt

  from base

  where criteria1

  group by state

  union

  select 'type 2' as type, state, sum(paid_amt) as paid_amt

  from base

  where criteria2

  group by state

  .....

  select 'type 10' as type, state, sum(paid_amt) as paid_amt

  from base

  where criteria10

group by state

; run;

A record can belong to several types. base table needs to be run 10 times in this sql. I wonder if there is a trick to produce the summary data quicker.

[Ksharp]

I remember Keith has done it by using Multi-label format before.

Maybe Keith will appear again and code it for you .

Ksharp

[manojinpec]

what is criteria1 2 3...

[Keith]

I'm obviously under pressure from @Ksharp here....

However, @manojinpec is right in that how the criteria is defined will determine how best to proceed.  If it is based on values of a single variable then multilabel format is a possibility.  If it is based on multiple conditions then I would suggest a datastep that creates variables Type1 - Type10 and stores a running total of paid_amt in each, thereby requiring only 1 pass of the data.

[manojinpec]

Thanks Keith!! Could you please send me an example on how to use multilabel format.I haev never used it.

[Keith]

Essentially it's a method that allows overlapping formats that can then be used in certain SAS procedures (SUMMARY, MEANS, TABULATE, REPORT).  If you google 'SAS multilabel format' then there are plenty of papers that describe this in more detail and provide some good examples.  The main things to note are :

• add the option 'multilabel' inside PROC FORMAT.
• add the option 'mlf' against the specific class variable inside the summary procedure.
• include the format statement inside the summary procedure.