topic Re: how do I convert dd-mmm-yy format to mmm-yy and calculate the # of days between dates in SAS Studio

how do I convert dd-mmm-yy format to mmm-yy and calculate the # of days between dates

AYoung60 — Sun, 17 Nov 2019 17:17:38 GMT

Hi All,

I'm still a novice with SAS programming and I just downloaded the free edition of SAS Studio 3 days ago so please bear with me.. i would really appreciate some specific help with these questions cause i've been searching for hours for solutions on google and just got more confused.Thank you in advance for any assistance provided.

1st how can i convert the variable hold_month values to month-year only (i.e no day)? As you can see below in the variable attributes, I tried reformatting the date9. format from the hold_placed_datetime variable to the format date7. to no avail.

2nd how can I calculate the # of days between the hold_placed_datetime and hold_filled_datetime?

Below is a copy of the dataset and proc contents I currently have:

hold_placed_datetime	hold_filled_datetime	source_location_code	pickup_location_code	hold_month	days_till_pickup
28-Oct-14	18-Oct-18	Main Library	Main Library	28-Oct-14
3-Mar-15	2-Feb-19	Forest Park Branch	Forest Park Branch	3-Mar-15
2-Jun-15	20-Dec-18	Harrison Branch	Covedale Branch	2-Jun-15
3-Jun-15	18-Oct-18	West End Branch	Delhi Township Branch	3-Jun-15
30-Jun-15	13-Dec-18	Hyde Park Branch	Hyde Park Branch	30-Jun-15
3-Jul-15	26-Dec-18	Bond Hill Branch	Pleasant Ridge Branch	3-Jul-15
3-Aug-15	18-Oct-18	Miami Township Branch	Delhi Township Branch	3-Aug-15
10-Sep-15	20-Feb-19	Green Township Branch	Green Township Branch	10-Sep-15
10-Sep-15	7-Jan-19	Harrison Branch	Harrison Branch	10-Sep-15
9-Oct-15	12-Jul-19	Wyoming Branch	Wyoming Branch	9-Oct-15

The CONTENTS Procedure

Data Set NameObservationsMember TypeVariablesEngineIndexesCreatedObservation LengthLast ModifiedDeleted ObservationsProtectionCompressedData Set TypeSortedLabel Data Representation Encoding

WORK.IMPORT1	2518839
DATA	6
V9	0
11/17/2019 11:28:03	72
11/17/2019 11:28:03	0
	NO
	NO

SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64
utf-8 Unicode (UTF-8)

Engine/Host Dependent InformationData Set Page SizeNumber of Data Set PagesFirst Data PageMax Obs per PageObs in First Data PageNumber of Data Set RepairsFilenameRelease CreatedHost CreatedInode NumberAccess PermissionOwner NameFile SizeFile Size (bytes)

65536
2775
1
908
871
0
/tmp/SAS_work689500000A1F_localhost.localdomain/SAS_workF77400000A1F_localhost.localdomain/import1.sas7bdat
9.0401M6
Linux
541858
rw-rw-r--
sasdemo
174MB
181927936

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat625143

days_till_pickup	Char	1	$1.	$1.
hold_filled_datetime	Num	8	DATE9.	DATE9.
hold_month	Num	8	DATE7.	DATE7.
hold_placed_datetime	Num	8	DATE9.	DATE9.
pickup_location_code	Char	21	$21.	$21.
source_location_code	Char	21	$21.	$21.

Re: how do I convert dd-mmm-yy format to mmm-yy and calculate the # of days between dates

mkeintz — Sun, 17 Nov 2019 17:31:23 GMT

@AYoung60

Since dates are internally stored as the number of days after 01jan1960, you can calculate the number of days between hold_filled_datetime and hold_placed_datetime as

N_of_days = hold_filled_datetime-hold_placed_datetime;

As to the display of hold_month, you could drop display of the day-of-month component by assigning the format MONYY7. But remember the internal value is unchanged, so you could also subtract hold_month minus some other date.

Re: how do I convert dd-mmm-yy format to mmm-yy and calculate the # of days between dates

Tom — Sun, 17 Nov 2019 18:04:56 GMT

The difference between using 7 characters instead of 9 when displaying dates with the DATE format is just that there is only room for two digits of the year. So 2019 and 1919 will be be displayed as 19.

There are many other formats that work for date values that you can choose from. Look at the documentation:

http://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#n0p2fmevfgj470n17h4k9f27qjag.htm

So you might want to consider these: MMYY, MONYY, YYMM, YYMON

Note that change the format just changes how the value is displayed, it does not change the actual value. If you want to convert a date to the first day of a month you can use the INTNX() function.

hold_month=intnx('month',hold_filled_date,0,'b');
format hold_month yymm7.;

Note that DATETIME values are stored as number of seconds and DATE values are number of days. It does not make sense to name variable as HOLD_FILLED_DATETIME and attach the DATE format to it. Either the name is right and the value is in seconds, in which case trying to display it as if it was a date will result in a date way in the future, or the format is right in which case the variable name will confuse users of the dataset.

Re: how do I convert dd-mmm-yy format to mmm-yy and calculate the # of days between dates

Cynthia_sas — Sun, 17 Nov 2019 18:17:04 GMT

Hi:

Internally, it appears that your date values are really just DATE values stored as numeric variables. It does not appear that they are actually DATETIME variables. Assuming this to be the case, then you could just use either simple arithmetic to determine duration or you could use the fancier interval functions. Let's start with a simple example. I already have THIS data:

But, even though we can all read those dates, they are stored internally as the number of days since Jan 1, 1960, which is how SAS dates are stored internally. In the data set in storage, the dates look like this:

However, with a SAS-defined format, I can control the appearance of those dates, as shown below:

So assuming that you just want to alter how the numeric date values appear, that can be accomplished with a FORMAT statement. We teach the FORMAT statement in our free Programming 1 e-learning class.

If you want to find the number of days or number of years between the hired date and the promoted date, then because the dates are stored internally as numbers, you can do simple arithmetic. (or, there are fancier functions, but let's keep it simple for now.)

Here's the entire program, it includes the data for the WORK.FAKEDATA file that was used initially. You should be able to run everything.

data fakedata;
  length name $10 jobtitle $15;
  infile datalines dlm=',';
  input name $ hired : date9. promoted : date9. jobtitle $;
return;
datalines;
Alan,15Jan1990,23Feb1995,Accountant
Barbara,15Nov2000,01Jan2003,Biophysicist
Chester,03Mar1999,21Jun2004,Chef
Diana,29Sep2014,19May2016,Deputy
Edward,01Jan2015,29Jul2018,Economist
;
run;

proc print data=fakedata;
  title '1) Use formats to control display';
  var name hired promoted jobtitle;
  format hired mmddyy10. promoted date9.;
run;

proc print data=fakedata;
  title '2) Use DIFFERENT formats to control display';
  var name hired promoted jobtitle;
  format hired monyy5. promoted mmddyy10.;
run;

proc print data=fakedata;
  title '3) Use DATE9. format to control display';
  var name hired promoted jobtitle;
  format hired promoted date9.;
run;

proc print data=fakedata;
  title '4) Show internal numbers for values';
  var name hired promoted jobtitle;
  format hired promoted;
run;

title;


data howlong;
  set fakedata;
  howlong_in_days = promoted - hired;
  howlong_in_years = howlong_in_days / 365.25;
run;

proc print data=howlong;
  title 'How Long in Original Job';
  var name hired promoted jobtitle 
      howlong_in_days howlong_in_years;
  format hired mmddyy10. promoted monyy5.;
run;
title;

Here's a good blog post on interval processing using SAS Functions: https://blogs.sas.com/content/iml/2017/05/15/intck-intnx-intervals-sas.html .

Hopes this helps explain how to use the FORMAT statement and how to simply calculate duration, IF your date variables are stored as numeric variables that represent the number of days since Jan 1, 1960.

Cynthia

Re: how do I convert dd-mmm-yy format to mmm-yy and calculate the # of days between dates

AYoung60 — Wed, 20 Nov 2019 20:22:18 GMT

Thank you for your help Tom. Appreciate it!

Re: how do I convert dd-mmm-yy format to mmm-yy and calculate the # of days between dates

AYoung60 — Wed, 20 Nov 2019 20:23:19 GMT

Ok. Thank you!