topic Filling in Missing Data that Really is Not Missing in SAS Programming

Filling in Missing Data that Really is Not Missing

BCNAV — Mon, 02 Dec 2019 18:14:26 GMT

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!

Re: Filling in Missing Data that Really is Not Missing

PeterClemmensen — Mon, 02 Dec 2019 18:28:52 GMT

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;

Re: Filling in Missing Data that Really is Not Missing

s_lassen — Tue, 03 Dec 2019 08:56:10 GMT

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.

Re: Filling in Missing Data that Really is Not Missing

vineelvarma — Wed, 04 Dec 2019 06:29:00 GMT

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;