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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.