DATA Step, Macro, Functions and more

proc sql summary alternative

Reply
Contributor
Posts: 57

proc sql summary alternative

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.

Super User
Posts: 10,028

proc sql summary alternative

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

Maybe Keith will appear again and code it for you . Smiley Happy

Ksharp

Frequent Contributor
Posts: 139

proc sql summary alternative

what is criteria1 2 3...

Regular Contributor
Posts: 151

proc sql summary alternative

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.

Frequent Contributor
Posts: 139

proc sql summary alternative

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

Regular Contributor
Posts: 151

proc sql summary alternative

Posted in reply to manojinpec

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.
Ask a Question
Discussion stats
  • 5 replies
  • 407 views
  • 0 likes
  • 4 in conversation