BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mauri0623
Quartz | Level 8

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_numberTask_numbercustomerBill_amt
97008003693WHSOTN0001097-WASHINGTON HEADQUARTERS SERVICES-DODH91268100000
97008880693DSCAOTN0002097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013100000
3572599693DCPSOTN0003DISTRICT OF COLUMBIA PUBLIC SCHOOLS50000
 693DCPSOTN0003_NREVDISTRICT OF COLUMBIA PUBLIC SCHOOLS 
97008003693WHSOTN0001097-WASHINGTON HEADQUARTERS SERVICES-DODH91268200000
97008880693DSCAOTN0002097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013200000
3572599693DCPSOTN0003DISTRICT OF COLUMBIA PUBLIC SCHOOLS670000
 693DCPSOTN0003_NREVDISTRICT OF COLUMBIA PUBLIC SCHOOLS 
97008003693WHSOTN0001097-WASHINGTON HEADQUARTERS SERVICES-DODH91268300000
97008880693DSCAOTN0002097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013300000
3572599693DCPSOTN0003DISTRICT OF COLUMBIA PUBLIC SCHOOLS80000
 693DCPSOTN0003_NREVDISTRICT OF COLUMBIA PUBLIC SCHOOLS 
    
Table 2   
project_numberTask_numbercustomerBill_amt
97008003693WHSOTN0001097-WASHINGTON HEADQUARTERS SERVICES-DODH91268100000
97008880693DSCAOTN0002097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013100000
3572599693DCPSOTN0003DISTRICT OF COLUMBIA PUBLIC SCHOOLS50000
3572599693DCPSOTN0003_NREVDISTRICT OF COLUMBIA PUBLIC SCHOOLS 
97008003693WHSOTN0001097-WASHINGTON HEADQUARTERS SERVICES-DODH91268200000
97008880693DSCAOTN0002097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013200000
3572599693DCPSOTN0003DISTRICT OF COLUMBIA PUBLIC SCHOOLS670000
3572599693DCPSOTN0003_NREVDISTRICT OF COLUMBIA PUBLIC SCHOOLS 
97008003693WHSOTN0001097-WASHINGTON HEADQUARTERS SERVICES-DODH91268300000
97008880693DSCAOTN0002097-DEFENSE SECURITY COOPERATION AGENCY-DODHQ0013300000
3572599693DCPSOTN0003DISTRICT OF COLUMBIA PUBLIC SCHOOLS80000
3572599693DCPSOTN0003_NREVDISTRICT OF COLUMBIA PUBLIC SCHOOLS 
1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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;
mauri0623
Quartz | Level 8

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

novinosrin
Tourmaline | Level 20

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;

 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 613 views
  • 0 likes
  • 3 in conversation