BookmarkSubscribeRSS Feed
Steelers_In_DC
Barite | Level 11

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

1 REPLY 1
Astounding
PROC Star

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 604 views
  • 0 likes
  • 2 in conversation