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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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