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 :
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.