Date:         Wed, 16 Aug 2000 10:32:50 -0700
Reply-To:     Philip Whittall <philip.whittallNOphSPAM@UNILEVER.COM.INVALID>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Philip Whittall <philip.whittallNOphSPAM@UNILEVER.COM.INVALID>
Organization: http://www.remarq.com: The World's Usenet/Discussions Start Here
Subject:      Re: array help

As I read it your proc transpose
statement only generates columns
for the sku's you have because
of the ID statement.

try using

sumed = sum (of _:);

array acctnos{*} _: ;

assuming they are the only variables
that start with _

If that fails, you can use
MACRO facility in SQL
to get the desired names into
a macro.

PROC SQL;
     RESET NOPRINT;
     SELECT NAME INTO :SKULIST
     SEPERATED BY ' '
     FROM DICTIONARY.COLUMNS
     WHERE LIBNAME='WORK'
     AND MEMNAME='CAT1A'
     AND TYPE='num'
     AND NAME LIKE '_%'
     AND NAME ^="ACCT_NO"
;
%PUT &SKULIST;

The %PUT is just a check that
you've got what you expect.

The use &SKULIST for the variable
list in the rest of your code.

Last, but not least, if you really
want to practice your SQL you can
achieve your goal (the proportion
of each accounts total accounted for
by a given sku) by using straight
SQL.

PROC SQL;
CREATE VIEW TOTALS AS
SELECT SUM(COUNT) AS SKU_TOT,
       ACCT_NO FROM CAT1
       GROUP BY ACCT_NO;
CREATE TABLE RESULT AS
       SELECT A.ACCT_NO,A.SKU_CD,A.COUNT,
              (A.COUNT/B.SKU_TOT) AS RATIO
       FROM CAT1 A RIGHT JOIN TOTALS B
       ON A.ACCT_NO=B.ACCT_NO
;

gives you what you want in the table
result. Note the format of this table
is much more amenable for use in
PROC TABULATE etc.




* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful