Date:         Wed, 17 Jul 2002 15:05:56 -0400
Reply-To:     "Huang, Ya" <ya.huang@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <ya.huang@PFIZER.COM>
Subject:      Re: COUNT RECORD
Comments: To: mto <mto@ADMIN4.HSC.UTH.TMC.EDU>
Content-Type: text/plain

Use SQL will be easier:

data xx;
input DEPT DIV $ ACCT $ INV $ AMT;
cards;
111   AA  X1  PO1   5.00
111   AB  X2  PO2   3.00
111   AA  X1  SO1   7.00
111   AA  X1  SO1   3.00
;

options nocenter;
proc sql;
select distinct dept,substr(inv,1,2) as invn,
 count(distinct inv) as no, sum(amt) as amt
from xx
group by invn
;
----------
    DEPT  invn            no       amt
--------------------------------------
     111  PO               2         8
     111  SO               1        10


Kind regards,

Ya Huang
-----Original Message-----
From: mto [mailto:mto@ADMIN4.HSC.UTH.TMC.EDU]
Sent: Wednesday, July 17, 2002 11:28 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: COUNT RECORD


I have this list:

DEPT      DIV     ACCT       INV       AMT
111        AA        X1        PO1            $5.00
111        AB        X2         PO2            $3.00
111        AA        X1         SO1            $7.00
111        AA        X1        SO1             $3.00  (same invoice #)

What I need to do is a report which gives me the dept. and count how
many invoices (using the first 2 digits number) and the amount.  The
result should look like:

Dept      Invoce #       Amount
111    PO        2            $8.00
111   SO        1            $10.00

What I did was set up the sub-string for first two-digits number:

        LENGTH LEV $5.;
         IF SUBSTR(INV,1,2)='PO' THEN LEV='1. PO';
        ELSE IF SUBSTR(INV,1,2)='SO' THEN LEV='2.SO';          etc....

Then sort the data by DEPT,LEV.  Then I used these statements:

DATA DEPT2; SET DEPT;
    BY DEPT LEV INV;
    RETAIN CNT TOT;
    FORMAT CNT $10.;
    FORMAT TOT COMMA12.2;
       IF FIRST.LEV THEN DO:
           CNT=0;  TOT=0;
      END;
           CNT + 1;
           TOT + AMT;
      IF LAST.LEV THEN OUTPUT;

Somehow, it doesn't come out right!!!!

Any help is very much appreciated.

Mia