topic Re: Catch Excel autofill errors after importing data in SAS Programming

Catch Excel autofill errors after importing data

Stalk — Tue, 10 Jan 2023 20:38:48 GMT

I've a dataset that has wrong dates created off of a excel drag function. How can I compare and identify the dates that are exactly one day apart in the sequence to alert the team?

Thank you

ProductID	Date_shipped
P3456	10/2/2023
R2342	10/3/2023
H9875	10/4/2023
L5543	10/5/2023

Re: Catch Excel autofill errors after importing data

Tom — Tue, 10 Jan 2023 21:18:50 GMT

Use the DIF function.

data want;
  set have;
  days=dif(date_shipped);
run;

Re: Catch Excel autofill errors after importing data

ballardw — Tue, 10 Jan 2023 21:32:30 GMT

Do you have other identification information such as a purchase order/bill of lading/ what have you? It would likely be a good idea to only report these one day increments in the case of some group identified as such.

Re: Catch Excel autofill errors after importing data

Stalk — Wed, 11 Jan 2023 00:18:02 GMT

Thank you.

DIF function works, but is there any similar function to DIF to use in Proc SQL?

ERROR: The DIF function is not supported in PROC SQL, it is only valid within the DATA step.

I have all my filters in SQL. Just for handling DIF function I have to recreate the whole dataset and order it again.

Re: Catch Excel autofill errors after importing data

Stalk — Wed, 11 Jan 2023 00:28:26 GMT

SAS programming is becoming simpler and easy to handle. Way to Go!!!!!!!
Thank you so much for the quick and best solution

Re: Catch Excel autofill errors after importing data

Tom — Wed, 11 Jan 2023 00:41:09 GMT

@Stalk wrote:

Thank you.

DIF function works, but is there any similar function to DIF to use in Proc SQL?

ERROR: The DIF function is not supported in PROC SQL, it is only valid within the DATA step.

I have all my filters in SQL. Just for handling DIF function I have to recreate the whole dataset and order it again.

SQL by its very nature knows nothing about the ORDER of observations so it is a terrible choice to do something like this.

Your second statement makes no sense at all. How are going to create a new VARIABLE without creating a new dataset, whether you use SQL query or a data step?

Note that you can create views using either PROC SQL code or data step code you want to avoid making multiple physical copies of the data.