NetNews Usenet Archive 1992 #19/NN_1992_19.iso/spool/bit/listserv/sasl/3910

Text File | 1992-08-26 | 7.3 KB | 256 lines

Comments: Gated by [e-mail]
Path: sparky!uunet!paladin.american.edu!auvm!GTE.COM!GLG2
X-Mailer: ELM [version 2.3 PL10]
Message-ID: <[e-mail]>
Newsgroups: bit.listserv.sas-l
Date:         Wed, 26 Aug 1992 13:41:33 EDT
Reply-To:     Gail Gill <[e-mail]>
Sender:       "SAS(r) Discussion" <[e-mail]>
From:         Gail Gill <[e-mail]>
Subject:      SQL responses
Comments: To: [e-mail]
Lines: 242

I'd like to add to that list of thanks Richard Wright, and Joseph
Schunk.

>Would you be kind enough to send me a copy of their responses, or else
>post a summary to the list? Thanks much in advance.

>- anup k. roy, u. of illinois at urbana-champaign

No problem, I kept a file and have done some editing of the responses.
I wound up trying a version of what Kernon sent, but since I was also
trying to do something else with it, my convoluted version did not
work. What I wound up doing was as Richard Wright suggested, but
again, a convoluted version, since I needed to do some other things
with it as well. I hope this helps you out.

Gail Gill
[e-mail]



From Bob Upson:
    (this specifically ignores your req for sql- if you've a reason for
using sql, feel free to discard.)
    sort the data by id,transtype, cost,
retain a counter, when the cost within type within id changes create the
composite var, output, and reset the counter.
code frag:
   ...; by id tipe cst; cnt+1; if last.cst then do;
compos=put(cnt,?.) :: '*' :: put(cst,?.); output;cnt=0;end;
(drop cst cnt;)

From Bob Snyder:
E-ADDR:    [e-mail]
NAME:      Bob Snyder
COMPANY:   Texas Instruments, Sherman, TX
PHONE:     [phone]
FAX:       [phone]

Try the following:

data sasuser.gailgill;

  length Cust 8 item $ 1 cost 8;

  format cost dollar7.2;

  input Cust item cost;

cards;
 1      A      2.00
 1      A      2.00
 1      A      2.00
 1      B      6.00
 1      B      6.00
 2      A      2.00
 2      C      4.00
 2      D      5.00
 2      E      7.00
;

run;

proc sql;
  select cust, item, put( sum(cost), dollar7.2 ) as cost
    from sasuser.gailgill
      group by cust,item;

============================== OUTPUT ===========================

    CUST  ITEM  COST
   -----------------------
     1  A       $6.00
     1  B      $12.00
     2  A       $2.00
     2  C       $4.00
     2  D       $5.00
     2  E       $7.00



    This isn't an SQL solution, but assuming COST is a numeric variable, I'd
do something like this ;

data temp;
input @2 cust 1. @9 item $1. @16 cost 4.2 ;
* Cust   item   cost ;
cards;
 1      A      1.23
 1      A      1.23
 1      A      1.23
 1      B      8.77
 1      B      8.77
 2      A      1.23
 2      C      7.55
 2      D      6.81
 2      E      6.33
;
run;
proc sort data=temp(rename=(cost=unitcost)) ;
  by cust ;
proc freq data=temp;
  by cust ;
  tables item /list out=results(rename=(count=cost) drop=percent) ;
  weight unitcost ;
  run;

---------------------------------------------------------------------------
Name         Steve James                          The new computer's a piece
Mail Address [e-mail]              of junk, I'd really wish
Real address Centers for Disease Control          they'd sell it.  It never
             1600 Clifton Road NE, Mailstop F36   does a thing I want, only
             Atlanta, GA  30333                   what I tell it.
             [phone]                                  Author Unknown

From: Melvin Klassen <[e-mail]>

SELECT     CUST ITEM SUM(COST)
 ORDER BY  CUST ITEM



I've included a SQL program to handle your request below.  Note that if
you just wanted a report, you could leave off the CREATE TABLE... part.
Also, this program works even if the cost isn't the same for each ITEM.
This could also be done easily enough using PROC SUMMARY.

Kernon Gibes
Internet: [e-mail]

___ SAS program ________________________________________________________

data long;
 input Cust   item $  cost;
cards;
 1      A      10
 1      A      10
 1      A      10
 1      B      15
 1      B      15
 2      A      10
 2      C      4
 2      D      5
 2      E      6
;
run;

proc sql;
  create table short as
  select cust, item, sum(cost) as cost
  from long
  group by cust, item
  ;
quit;

proc print data=short;
run;

___ SAS output _________________________________________________________

                      OBS    CUST    ITEM    COST

                       1       1      A       30
                       2       1      B       30
                       3       2      A       10
                       4       2      C        4
                       5       2      D        5
                       6       2      E        6


Assuming you aren't trying to get symbolic results (and I really wish
SAS would develope a symbolic math processor), this sounds like the prefect
candidate for PROC TABULATE or SUMMARY / MEANS.

PROC TABULATE DATA=yourdata ;
  CLASS cust item ;
  VAR cost ;
  TABLE cust*item,cost*SUM ;

will give you a nice table looking very much like your example.

You could create an output data set for further processing using either of
the latter two procs:

PROC SUMMARY DATA=yourdata NWAY ;
  CLASS cust item ;
  VAR cost ;
  OUTPUT OUT=outdata SUM= ;

will create an aggregated data set of costs by cust and item. As an added
special advantaged, the data set will be sorted by cust and item, even if
the input data set is not sorted. BTW, note the NWAY option - this yields
unique cross-products of the two CLASS variables. If you don't use it, you
will get grand totals and totals of cost for cust and item.

Needless to say, there are a multitude of options you can fiddle with to
that just right look.

       ============================================
      =  RRRRR ------ CCCCC ----- WW          WW =
     = RR     RR  CC        CC - WW          WW =
    = RR    RR - CC           - WW          WW =
   = RRRRRRR -- CC           - WW          WW = Richard Wright
  = RR    RR - CC           - WW    WW    WW = Tx. Leg. Bud. Board
 = RR     RR -- CC    CC --- WW  WW  WW  WW = Internet:[e-mail]
= RR     RR ---- CCCCC ------ WWW --- WWW  = Compuserve: 70673,1131
===========================================


proc sql;

  select
    cust,
    item,
    cost as unitcost,
    count(*) as quantity,
    (unitcost * quantity) as extended
    from
      mytable
   group  by
     cust, item
;

  +------------------------------------------------------------+
  |  MCI Mail:  JSchunk or 100-7026                            |
  |  Telex (MCI/WUI): 6501007026   Ans: 6501007026MCI UW       |
  |  Internet:  [e-mail]                            |
  |  X.400: G=joseph; S=schunk; ADMD=mci; C=us;                |
  +------------------------------------------------------------+

Wouldn't this be the answer:

select cust , item , sum(cost) as cost
from yourdata
group by cust , item
order by cust , item
;
_____________________________________________________________________________
 Frank Poppe             <[e-mail]>                tel:   [phone]
 Institute for Road Safety Research SWOV                fax:   [phone]
    ,,,  ,, ,, ,,  .... ,, ,,                           mail:      PO Box 170
   ||,,, || || || :: :: || ||                            2260 AD Leidschendam
   ,,,|~ ||,'|,~  :: :' ||,~                                  the Netherlands