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 |
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;
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;
Thank you for your response. I accepted your method as a solution.
Anytime 🙂
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;
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.