SQL Group By in SAS - alternative?

Question

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.

By the use of `rsubmit` & `endrsubmit` it appears that you are submiting your query to a remote SAS server - how are checking the output dataset `wpffasdekpr2`? Are you downloading to your local SAS session using `PROC DOWNLOAD`? could it be possible that you are actually see a stale copy of `wpffasdekpr2`? SQL group by in SAS will work pretty much the same way as it does in anyother DB except for a couple of quirks? let's leave those for some other time. — , Feb 13 '14 at 15:35
I'm sure it's not stale since the table didn't exist before the query. Viewing it using the built in viewer in SAS. — DavveK, Feb 13 '14 at 15:49
It appears `nationalkey` is a composite key made up of (as far as I can see form the image)`{polnum, ORDNNR, FSNR, BRKD, FVPKD, MNR, MLOPNR, EXPOSURETYPE }` Could it be possible there is something still there in the `nationalkey` that is hidden. Can you try running the same query but exclude `nationalkey` from both `SELECT` & `group by` and see if you notice a decrease in the no. of rows in `wpffasdekpr2`. I'm guessing `nationalkey` is some sort of primary key. — , Feb 13 '14 at 16:01

score 2 · Answer 1 · answered Feb 13 '14 at 15:58

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;

Thank you! I didn't realise the group by works on underlying rather than formatted. The coverto/coverfrom are formatted numbers (YYMMDD10.) also exposuretype and COActivityCOde are foratted ($10). I will try the numbered syntax. — DavveK, Feb 13 '14 at 16:03
hmm - YYMMDD10. is a date (not time) format, so that can't be the issue. Does COActivityCode have a LENGTH longer than $10? If so, it could be that.. — Allan Bowe, Feb 13 '14 at 16:07

SQL Group By in SAS - alternative?

1 Answers1