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.
I remember Keith has done it by using Multi-label format before.
Maybe Keith will appear again and code it for you .
Ksharp
what is criteria1 2 3...
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.
Thanks Keith!! Could you please send me an example on how to use multilabel format.I haev never used it.
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 :
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.