topic proc sql summary alternative in SAS Programming

proc sql summary alternative

aha123 — Tue, 27 Mar 2012 23:18:45 GMT

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.

proc sql summary alternative

Ksharp — Wed, 28 Mar 2012 06:48:26 GMT

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

Maybe Keith will appear again and code it for you .

Ksharp

proc sql summary alternative

manojinpec — Wed, 28 Mar 2012 07:03:27 GMT

what is criteria1 2 3...

proc sql summary alternative

Keith — Wed, 28 Mar 2012 09:03:54 GMT

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.

proc sql summary alternative

manojinpec — Wed, 28 Mar 2012 09:36:44 GMT

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

proc sql summary alternative

Keith — Wed, 28 Mar 2012 09:50:42 GMT

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.