topic Re: filling out missing with the previous value in SAS Programming

filling out missing with the previous value

mauri0623 — Wed, 21 Aug 2019 14:05:40 GMT

I have two distinct questions. One question is about how to convert a = today(); to be formatted as 08212019.

Question 2 is about how to make table two from table one. Project_number could have > 1 task_number. I want to be able to fill the missing project_number by the previous one.

Table 1
project_number	Task_number	customer	Bill_amt
97008003	693WHSOTN0001	097-WASHINGTON HEADQUARTERS SERVICES-DODH91268	100000
97008880	693DSCAOTN0002	097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013	100000
3572599	693DCPSOTN0003	DISTRICT OF COLUMBIA PUBLIC SCHOOLS	50000
	693DCPSOTN0003_NREV	DISTRICT OF COLUMBIA PUBLIC SCHOOLS
97008003	693WHSOTN0001	097-WASHINGTON HEADQUARTERS SERVICES-DODH91268	200000
97008880	693DSCAOTN0002	097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013	200000
3572599	693DCPSOTN0003	DISTRICT OF COLUMBIA PUBLIC SCHOOLS	670000
	693DCPSOTN0003_NREV	DISTRICT OF COLUMBIA PUBLIC SCHOOLS
97008003	693WHSOTN0001	097-WASHINGTON HEADQUARTERS SERVICES-DODH91268	300000
97008880	693DSCAOTN0002	097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013	300000
3572599	693DCPSOTN0003	DISTRICT OF COLUMBIA PUBLIC SCHOOLS	80000
	693DCPSOTN0003_NREV	DISTRICT OF COLUMBIA PUBLIC SCHOOLS

Table 2
project_number	Task_number	customer	Bill_amt
97008003	693WHSOTN0001	097-WASHINGTON HEADQUARTERS SERVICES-DODH91268	100000
97008880	693DSCAOTN0002	097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013	100000
3572599	693DCPSOTN0003	DISTRICT OF COLUMBIA PUBLIC SCHOOLS	50000
3572599	693DCPSOTN0003_NREV	DISTRICT OF COLUMBIA PUBLIC SCHOOLS
97008003	693WHSOTN0001	097-WASHINGTON HEADQUARTERS SERVICES-DODH91268	200000
97008880	693DSCAOTN0002	097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013	200000
3572599	693DCPSOTN0003	DISTRICT OF COLUMBIA PUBLIC SCHOOLS	670000
3572599	693DCPSOTN0003_NREV	DISTRICT OF COLUMBIA PUBLIC SCHOOLS
97008003	693WHSOTN0001	097-WASHINGTON HEADQUARTERS SERVICES-DODH91268	300000
97008880	693DSCAOTN0002	097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013	300000
3572599	693DCPSOTN0003	DISTRICT OF COLUMBIA PUBLIC SCHOOLS	80000
3572599	693DCPSOTN0003_NREV	DISTRICT OF COLUMBIA PUBLIC SCHOOLS

Re: filling out missing with the previous value

PeterClemmensen — Wed, 21 Aug 2019 14:21:53 GMT

1) Use appropriate format

data test;
	date=today();
	format date mmddyyn8.;
run;

2) Do something like this

data Table1;
input project_number Task_number :$100. customer :$100. Bill_amt;
infile datalines dlm=',';
datalines;
97008003,693WHSOTN0001,097-WASHINGTON HEADQUARTERS SERVICES-DODH91268,100000
97008880,693DSCAOTN0002,097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013,100000
3572599,693DCPSOTN0003,DISTRICT OF COLUMBIA PUBLIC SCHOOLS,50000
 ,693DCPSOTN0003_NREV,DISTRICT OF COLUMBIA PUBLIC SCHOOLS,
97008003,693WHSOTN0001,097-WASHINGTON HEADQUARTERS SERVICES-DODH91268,200000
97008880,693DSCAOTN0002,097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013,200000
3572599,693DCPSOTN0003,DISTRICT OF COLUMBIA PUBLIC SCHOOLS,670000
 ,693DCPSOTN0003_NREV,DISTRICT OF COLUMBIA PUBLIC SCHOOLS,
97008003,693WHSOTN0001,097-WASHINGTON HEADQUARTERS SERVICES-DODH91268,300000
97008880,693DSCAOTN0002,097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013,300000
3572599,693DCPSOTN0003,DISTRICT OF COLUMBIA PUBLIC SCHOOLS,80000
 ,693DCPSOTN0003_NREV,DISTRICT OF COLUMBIA PUBLIC SCHOOLS,
;

data Table2(drop=_:);
	set Table1;
	retain _project_number _Bill_amt;

	if project_number ne ' ' then _project_number=project_number;
		else project_number=_project_number;
	if Bill_amt ne . then _Bill_amt=Bill_amt;
		else Bill_amt=_Bill_amt;
run;

Re: filling out missing with the previous value

mauri0623 — Wed, 21 Aug 2019 15:31:16 GMT

Thank you for your response. I accepted your method as a solution.

Re: filling out missing with the previous value

PeterClemmensen — Wed, 21 Aug 2019 15:32:28 GMT

Anytime 🙂

Re: filling out missing with the previous value

novinosrin — Wed, 21 Aug 2019 15:51:32 GMT

Hello @mauri0623 I am pleased you received an nice LOCF straight forward and neat solution. I would like to suggest to use missing function rather than representing with a ' '(character) or . (numeric) constant. It's much safer and convenient besides reading ease and does the job of course.

When you check for ordinary missing numeric values, you can use code that is similar to the following:

if numvar=. then do;

To check for a missing character value, you can use a statement that is similar to the following:

if charvar=' ' then do;

The MISSING function enables you to check for either a"character or numeric missing value",as in:

if missing(var) then do;