CODEWITHSUNDEEP
sasproc-sql
DavveK
DavveK

Reputation: 173

SQL Group By in SAS - alternative?

rsubmit;
proc sql;
create table wpffasdekpr2 as
select polnum, ordnnr, brkd, fsnr, fvpkd, mnr, mlopnr,coverfrom, coverto, nationalkey, exposuretype, COActivityCode, sum(COSumInsured) as SumIns, sum(AnnualBaseAmt) as SumAmt from wpffasdekpr
group by 
polnum, ordnnr, brkd, fsnr, fvpkd, mnr, mlopnr,coverfrom, coverto, nationalkey, exposuretype, COActivityCode;
quit;
endrsubmit;

The table this operates on looks like below:

enter image description here

I expected the first 4 rows to compress into one by this statement, and the two rightmost variables being summed. However, the statement has no effect. The output table looks like the input table. Two questions: 1. Why is this? in usual sql I'm positive it would work. 2. How can I achieve the expected result?

Log:

508  rsubmit;
NOTE: Remote submit to SESKUX01 commencing.
1391  proc sql;
1392  create table wpffasdekpr2 as
1393  select polnum, ordnnr, brkd, fsnr, fvpkd, mnr, mlopnr,coverfrom, coverto, nationalkey, exposuretype, COActivityCode, sum(COSumInsured) as COsumInsured2, sum(AnnualBaseAmt) as AnnualBaseAmt2
1393! from wpffasdekpr
1394  group by
1395  polnum, ordnnr, brkd, fsnr, fvpkd, mnr, mlopnr,coverfrom, coverto, nationalkey, exposuretype, COActivityCode;
NOTE: Compressing data set WORK.WPFFASDEKPR2 increased size by 5.17 percent.
      Compressed is 29614 pages; un-compressed would require 28159 pages.
NOTE: Table WORK.WPFFASDEKPR2 created, with 28778049 rows and 14 columns.

1396  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:54.66
      cpu time            1:41.68


NOTE: Remote submit to SESKUX01 complete.

Upvotes: 0

Views: 595

Answers (1)

Allan Bowe
Allan Bowe

Reputation: 12701

Are CoverFrom and CoverTo time or date values? If time, they may not be identical. May be worth checking whether any formats are applied to the other variables in the source dataset, as the group by operation will work on the underlying values (rather than the formatted / displayed results).

Failing that, I'd recommend re-executing your query, using one group by at a time. Also, the use of the following syntax (numbered group by) can avoid unintended errors:

 proc sql;
 create table wpffasdekpr2 as
    select polnum, ordnnr, brkd, fsnr, fvpkd, mnr, mlopnr,coverfrom, coverto
       ,nationalkey, exposuretype, COActivityCode
       ,sum(COSumInsured) as SumIns, sum(AnnualBaseAmt) as SumAmt 
    from wpffasdekpr
    group by 1,2,3,4,5,6,7,8,9,10,11,12;

Upvotes: 2

Related Questions