key=

[Steelers_In_DC]

I am looking through the advanced cert study guide and came across the following:

data profit;

set cert.dnunder;

set work.sale2000 (keep=routeid flightid date rev1st revbusiness revecon revcargo) key=flightdate;

Profit=sum(rev1st,revbusiness,revecon,revcargo,-expenses);

run;

There is one record that is pulled from 'cert.dnunder' that is not in 'work.sale2000'.  The fields for the sum function are not in that final record but it seems like joining using the 'key=' option retains the variables in the sum function.  If I run the following code the two data sets do not match because of that final record, profit's final record has rev variables, profitII's final record does not have the rev variables.  Is this acting accordingly, or am I correct in thinking the extra field should not have the rev variables populated?  I'll attach the entire code in case that is helpful.

data profitII;

merge  dnunder (in=a)

  sale2002(in=b keep=routeid flightid date rev1st revbusiness revecon revcargo);

by flightid date;

if a;

Profit=sum(rev1st,revbusiness,revecon,revcargo,-expenses);

run;

Thanks,

Mark

[Astounding]

Mark,

Any variables read from a SAS data set are automatically retained.  It doesn't matter if that SET statement uses KEY= or not.  That's why you're seeing values for ROUTEID, FLIGHTID, etc., even when a match is not found.

The usual way around this is to examine the variable _IORC_.  (Think Input-Output-Return-Code.)  When it's zero it means a match was found.  When it's nonzero, it's up to you to wipe out the retained values, along these lines:

if _iorc_ > 0 then do;

   call missing(routeid);

   call missing(flightid);

   _iorc_=0;

end;

Good luck.