When we bill aircraft, there are a number of systems that do the billing. There is one variable that will tie together the data from the systems. This variable is called the billing_flight_id. Suppose I have data that is:
billable_id | FLT_BIZ_UID | fdaas_flight_id | billing_flight_id | chargetype_code | ac_type | synonym | more variables |
97975800 | 33614638 | 33614638 | 23377506 | M | A343 | A343 | |
97975800 | 33614638 | 33614638 | 23377506 | M | A343 | A343 | |
97988982 | . | . | 23377506 | T | A343 | ||
98028109 | . | . | 23377506 | N | A343 | H/A343/* |
You can see that the billing_flight_id is the same for all 3 systems of billing charges (M,T,N). What I would like to do is:
For every billing_flight_id, if there is a FLT_BIZ_UID and fdaas_flight_id present as part of its billing process, fill in the missing FLT_BIZ_UID and fdaas_flight_id with the existing values of those variables. All other variables would be the same. So my output would be:
billable_id | FLT_BIZ_UID | fdaas_flight_id | billing_flight_id | chargetype_code | ac_type | synonym | more variables |
97975800 | 33614638 | 33614638 | 23377506 | M | A343 | A343 | |
97975800 | 33614638 | 33614638 | 23377506 | M | A343 | A343 | |
97988982 | 33614638 | 33614638 | 23377506 | T | A343 | ||
98028109 | 33614638 | 33614638 | 23377506 | N | A343 | H/A343/* |
The bold are the changes.
Thanks in advance!
Here is one way
data have;
input billable_id FLT_BIZ_UID fdaas_flight_id billing_flight_id chargetype_code $ ac_type $ synonym $;
datalines;
97975800 33614638 33614638 23377506 M A343 A343
97975800 33614638 33614638 23377506 M A343 A343
97988982 . . 23377506 T A343 .
98028109 . . 23377506 N A343 H/A343/
;
data want(drop=_:);
set have;
if FLT_BIZ_UID ne . then _FLT_BIZ_UID=FLT_BIZ_UID;
if fdaas_flight_id ne . then _fdaas_flight_id=fdaas_flight_id;
if FLT_BIZ_UID=. then FLT_BIZ_UID=_FLT_BIZ_UID;
if fdaas_flight_id=. then fdaas_flight_id=_fdaas_flight_id;
retain _:;
run;
One way to do it is this:
data want;
if 0 then set have; /* just to get variables in right order */
merge
have(drop=FLT_BIZ_UID fdaas_flight_id)
have(where=(FLT_BIZ_UID is not null and fdaas_flight_id is not null) keep=FLT_BIZ_UID fdaas_flight_id billing_flight_id)
;
by billing_flight_id;
run;
Just remember to sort by billing_flight_id first.
data bill;
input bill1 FLT fdaas billing;
cards;
97975800 33614638 33614638 23377506
97975800 33614638 33614638 23377506
97988982 . . 23377506
98028109 . . 23377506
;
run;
data b;
set bill;
retain x y;
if not missing(flt) then x=flt;
else flt=x;
if not missing(fdaas) then y=fdaas;
else fdaas=y;
drop x y;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: