[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: How to aggregate (sum) except for contributing zeros? Then sum is         zero.
From:       Nat Wooding <nathani () VERIZON ! NET>
Date:       2013-03-30 21:42:05
Message-ID: 000001ce2d8f$6c44e230$44cea690$ () verizon ! net
[Download RAW message or body]

Gimmie

I don't understand what you want to do with Key3. In your output, each obs
has key3=1 but the values are not always 1 in the input.

Nat Wooding

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Gimme
More
Sent: Saturday, March 30, 2013 11:58 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How to aggregate (sum) except for contributing zeros? Then sum is
zero.

Hi List,

I have a simple mathematical problem, just cant get one constraint solved:
Sum of RAWDATA by KEY1 and KEY2 (amongst others, the CLASS var KEY3 is the
one to be

"aggregated over"). Simple example (see INPUT, Key=1 and KEY=4):
RAWDATA110+220, SUMDATA is 330. Now comes the tricky part: Except for
zeros!

If any of the contributing RAWDATA values (by KEY1 and KEY2) eq zero, then
SUMDATA has to be set to 0 (see OUTPUT).

I know there should be a simple solution. Ive plagued myself the whole week
(including yesterday, sigh) with lengthy SAS macros loops.

I admitt right now Im just something between stuck or running on empty. I
hope somebodys still out there today.

Folks, I thank you a lot for any helping hand!

Cheers, Gimmi


Input:

DATE                       Key1                       Key2
Key3                SEX         AGE        RAWDATA

01.01.2003                     1                       2
1                       1                25                       200

01.01.2003                     1                       2
2                       1                25                       300

01.01.2003                     1                       2
3                       1                25                       *0 **
<-----***

01.01.2003                     1                       4
21                      1                25                       110

01.01.2003                     1                       4
22                      1                25                       220

01.01.2004                     1                       2
1                       1                25                       100

01.01.2004                     1                       2
2                       1                25                       150

01.01.2004                     1                       2
3                       1                25                       120

01.01.2004                     1                       4
21                      1                25                       130

01.01.2004                     1                       4
22                      1                25                       230


Desired Output:


DATE                       Key1                       Key2
Key3                       SEX         AGE         SUMDATA

01.01.2003                       1                       2
                     1                25               *0 **
**<-----**
*

01.01.2003                       1                       4
                     1                25               330


01.01.2004                       1                       2
                     1                25               370


01.01.2004                       1                       4
                     1                25               360
[prev in list] [next in list] [prev in thread] [next in thread]