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

Im New to SAS Base and have been given a project and this is the question

 

On average how many days does it take ADW to deliver products between the order date and shipment date? Show results on a monthly basis, this will help the business understand its low performing delivery months and identify the drivers.

  • The business would also like to know the order to shipment performance by regions, state and country.
  • Which product category and sub product categories is late shipment predominant to?

Please note: SLA for shipments is next day between the order date and ship date.

 

I have Identified the data that I need to use but I cant figure out the first step of calculating the days it takes to ship because the datdiff function doesnt work on the imported data.

 

The Dates Looks Like this on both order and ship dates.

08AUG2005:00:00:00

 

I tried to figure this out with just ship date and was using this code but I kept getting number insted of dates when it converted:

 

Data Date (Keep = EmployeeKey SalesTerritoryKey ShipDate DATEPART_SHIPDATE PUT_SHIPDATE INPUT_SHIPDATE );
Set project.dbo_factresellersales ;
Informat ShipDate ANYDTDTEw.;
DATEPART_SHIPDATE = datepart(ShipDate);
PUT_SHIPDATE = PUT(DATEPART_SHIPDATE, date9.);


Run;

 

I cant figure out how to convert the dates from datetime to date so I can use datdiff. 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
Data Date 
(Keep = EmployeeKey SalesTerritoryKey ShipDate DATEPART_SHIPDATE PUT_SHIPDATE );

Set project.dbo_factresellersales ;

Format DATEPART_SHIPDATE DATEPART_OrderDATE  mmddyy10. shipDate OrderDate  datetime21.;

DATEPART_SHIPDATE = datepart(ShipDate);
DATEPART_OrderDATE = datepart(orderDate);
datediff=datepart_orderdate-datepart_shipdate;

Run;

View solution in original post

9 REPLIES 9
ballardw
Super User

Without a date format applied to the date value you get an integer that is the number of days since 1 Jan 1960. To see the date apply a format such as mmddyy10. or date9. It looks like your DATEPART_SHIPDATE should be okay, just not formatted.

 

You would need two date values but I can't tell from you code if you have another or what it's name might be.

U12345
Calcite | Level 5

I have changed my code to include format but in the output data for ShipDate I get are asterix where the date is meant to be.  

 

Data Date (Keep = EmployeeKey SalesTerritoryKey ShipDate DATEPART_SHIPDATE PUT_SHIPDATE );
Set project.dbo_factresellersales ;
Format ShipDate mmddyy10. OrderDate mmddyy10.;
DATEPART_SHIPDATE = datepart(ShipDate);
PUT_SHIPDATE = PUT(DATEPART_SHIPDATE, date9.);


Run;

Reeza
Super User
Data Date 
(Keep = EmployeeKey SalesTerritoryKey ShipDate DATEPART_SHIPDATE PUT_SHIPDATE );

Set project.dbo_factresellersales ;

Format DATEPART_SHIPDATE DATEPART_OrderDATE  mmddyy10. shipDate OrderDate  datetime21.;

DATEPART_SHIPDATE = datepart(ShipDate);
DATEPART_OrderDATE = datepart(orderDate);
datediff=datepart_orderdate-datepart_shipdate;

Run;
U12345
Calcite | Level 5

Thanks this helped alot. I understand what I was doing wrong 

DanZ
Obsidian | Level 7
data import;
input
		employee : $10.
		sales_territory : $10. 
		order_date: mmddyy10. 
		ship_date: mmddyy10.;
format order_date ship_date mmddyy10.; *shows dates as dates instead of numbers;
cards;
John East 1/1/2015 1/5/2015
Bob East 1/1/2015 1/4/2015
John West 1/1/2015 1/3/2015
Bob West 1/1/2015 1/2/2015
John North 1/1/2015 1/1/2015
Bob North 1/1/2015 1/6/2015
;
run;

data have;
set import;
date_diff = intck('day',order_date,ship_date);
run;

proc means data=have;
class sales_territory;*Change to the category/categories that you're interested in; 
var date_diff;
output out=want
	n=cnt
	mean=avg;
run;

You're on the right track with datepart()

 

Dates are stored as numbers with 1/1/1960 = 1, 1/2/1960 = 2, &c. To see them as dates, you need to format them with something, like mmddyy10., or date9., &c.

 

You can show differences by subtracting them, or using the intck() function.

 

As far as displaying the dimensions and interactions with regard to averages, proc means will probably suit what you're doing.

 

 

U12345
Calcite | Level 5

I tired that but since my dates are in a datetime format it dosent seem to caluclate using intck(). 

Reeza
Super User

Then convert your datetimes to dates using DATEPART()

 

DanZ
Obsidian | Level 7

Convert to dates with the datepart() function before you calculate the difference between the dates.

U12345
Calcite | Level 5

Thanks alot, I just realised what i was doing wrong. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 9 replies
  • 1162 views
  • 0 likes
  • 4 in conversation