BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ammarhm
Lapis Lazuli | Level 10

Hi

I have imported an excel file containing two dates columns, formated as yymmdd

I am trying to find the difference between these two columns

I used the code below but it is returning some strange results

data want;

set have;

format regtime start_date yymmdd10.;

NumDays = intck( 'day', start_date, regtime );

run;


Any help is appreciated


Regards


1 ACCEPTED SOLUTION

Accepted Solutions
BurntDirt
Calcite | Level 5

I think it is the way that you are importing these.

If you just use a simple proc import, it will import these as numeric and not as SAS dates.

SAS dates are also numeric, but they reference the number of days since 1/1/1960.

What you are getting are the numeric values

Obs     Start_date        regtime

  1         120305         140224
  2         120319         140224
  3         120227         140224

You need to convert these to SAS dates or import them as SAS dates.

To convert them to SAS dates, you can do something like

data want;

     set have;

     sas_start_date = input(compress(put(start_date,best8.)),yymmdd6.);

     sas_regtime = input(compress(put(stat_date,best8.)),yymmdd6.);

     NumDays = sas_regtime - sas_start_date;

run;

View solution in original post

8 REPLIES 8
mohamed_zaki
Barite | Level 11

post sample from the have dataset

KachiM
Rhodochrosite | Level 12

Your CODE is Fine.

Better to see your example.

Jagadishkatam
Amethyst | Level 16

I believe the strange result is because of the date format used.

could you please convert the dates to date9. format and then use the intck function.

Thanks,

Jag

Thanks,
Jag
Patrick
Opal | Level 21

The date format applied has no influence on the result returned by the intck() function as the function uses the internal numeric value.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You don't actually need that function.  If both of your variables are date variables, then they are stored as the number of days since a certain timepoint.  Therefore if you subtract one from the other you get the number of days between:

num_days = start_date - regtime;

However, I also concur with the above posters, post example data as the term Regtime indicates that you are looking at a time variable not a date.  Or possible a date/time variable.  If so then just do:

num_days = start_date - datepart(regtime);

ammarhm
Lapis Lazuli | Level 10

Here is a sample of the file

The variables are not formated as date in the Excel file but I thought it wouldn't be a problem as I am applying a date format in the SAS code then

As I mentioned, the date formate here is yymmdd

Thanks in advance

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, as BurntDirt has mentioned those are just numbers, and it is just guessing what they represent, is 120305 = 12Mar05, or 05Mar12 or something else??  Also little tip, don't use proc import as it basically guesses what you want to do.  There are many ways to get data in, I would advise that flat text file delimited, e.g. CSV, is best.  Then write a datastep import program specifically tailored to the data you know best.

BurntDirt
Calcite | Level 5

I think it is the way that you are importing these.

If you just use a simple proc import, it will import these as numeric and not as SAS dates.

SAS dates are also numeric, but they reference the number of days since 1/1/1960.

What you are getting are the numeric values

Obs     Start_date        regtime

  1         120305         140224
  2         120319         140224
  3         120227         140224

You need to convert these to SAS dates or import them as SAS dates.

To convert them to SAS dates, you can do something like

data want;

     set have;

     sas_start_date = input(compress(put(start_date,best8.)),yymmdd6.);

     sas_regtime = input(compress(put(stat_date,best8.)),yymmdd6.);

     NumDays = sas_regtime - sas_start_date;

run;

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!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1832 views
  • 4 likes
  • 7 in conversation