Date:         Tue, 2 Sep 2003 13:46:04 -0400
Reply-To:     Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject:      Re: missing values
Comments: To: Mai To <Mai.To@UTH.TMC.EDU>
Content-Type: text/plain

Mai,

With one record per department (as your data shows) try

data w ;
   Merge rev exp;
   By dept;
   if missing (exp) then inc = sum ( 0 , rev ) ;
   else
      Inc=sum( 0, rev, -exp ) ;
   Format inc comma9.2;
run ;

To handle multiple records per department, I would avoid the merge issues by
summing the revenues and expenditures to one record per department first and
then using the above code.

IanWhitlock@westat.com

-----Original Message-----
From: Mai To [mailto:Mai.To@UTH.TMC.EDU]
Sent: Tuesday, September 02, 2003 11:54 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: missing values


I have these two datasets:

1.
Dept    rev
001     100
002     200
003     250
005     100


2.
Dept    exp
001     80
003     200
004     60
007     50

The result should look like this

Dept    rev             exp             inc
001     100             80              20
002     200                             200
003     250             200             50
004                     60              (60)
005     100                             100
007                     50              (50)

These are my codes:

Step1;
Merge rev exp;
  By dept;
  If rev=' ' then rev=.;
  If exp=' ' then exp=.;

Proc sort data=step1;
By dept;

Step2;
Set step1;
By dept;
Retain inc;
Format inc comma9.2;
If first.dept then inc=0;
  Inc=rev-exp;
If last.dept and inc ne 0 then output;


With the above codes, I received the message "missing values...."  What
should I do so the "missing values" thing would disappear?

Thanks,
Mia