topic Re: Convert dates including partial dates to numeric format in SAS Programming

Convert dates including partial dates to numeric format

deepakg77717 — Mon, 29 Mar 2021 14:15:48 GMT

Hi All,

Trying to convert the dates. Kindly help

data a_;
input date $20.;
datalines ;

2020-10-20

2020-10

2020
;
run;

data b_;
format dtn yymmdd10.;
set a_;
if length(date) = 10 then do;
dtn=input(date,yymmdd10.);
end;
if length(date) = 7 then do;
dtn=input(compress(date,'-'),yymmn6.);
end;

run;

output:

dtn date

2020-10-20 2020-10-20

2020-10-01 2020-10

Desired output:

dtn date

2020-10-20 2020-10-20

2020-10 2020-10

Due to format applied,01 is added.

Re: Convert dates including partial dates to numeric format

PaigeMiller — Mon, 29 Mar 2021 14:48:24 GMT

SAS does not recognize partial dates, and so these cannot be converted to numeric, except as you have seen where 2020-10 is internally represented as October 1, 2020, and shown as 2020-10-01. Similarly, 2020 is a partial date and would represented internally as January 1, 2020.

So, given this is the case, what do you really want? If you really have to retain partial dates, just leave the strings as character.

Re: Convert dates including partial dates to numeric format

deepakg77717 — Mon, 29 Mar 2021 15:09:36 GMT

Thanks Paige Miller.. I got the reason..

Yes, I need the partial dates to be stored..

Could you please elaborate your suggestion. Since the column is defined as numeric, I am not able to assign character values. I get a Note: Invalid numeric data '2020-10' at ....

Re: Convert dates including partial dates to numeric format

PaigeMiller — Mon, 29 Mar 2021 15:20:56 GMT

@deepakg77717 wrote:

Could you please elaborate your suggestion.

Do not convert to numeric, then the partial dates are preserved.

Re: Convert dates including partial dates to numeric format

Reeza — Mon, 29 Mar 2021 15:27:54 GMT

SAS does not have a way to store partial dates, each date must have a day, month and year component.

An exception to this are datetimes in the ISO 8601 formats.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=leforinforref&docsetTarget=p1a0qt18rxydrkn1b0rtdfh2t8zs.htm&locale=en#n07150tx0ht2whn18j1f963j1kmc

So if you want to store your dates as shown you require a character variable or use a DATETIME variable with your hour minute seconds set to 0.

@deepakg77717 wrote:

Hi All,

Trying to convert the dates. Kindly help

data a_;
input date $20.;
datalines ;

2020-10-20

2020-10

2020
;
run;

data b_;
format dtn yymmdd10.;
set a_;
if length(date) = 10 then do;
dtn=input(date,yymmdd10.);
end;
if length(date) = 7 then do;
dtn=input(compress(date,'-'),yymmn6.);
end;

run;

output:

dtn date

2020-10-20 2020-10-20

2020-10-01 2020-10

Desired output:

dtn date

2020-10-20 2020-10-20

2020-10 2020-10

Due to format applied,01 is added.

Re: Convert dates including partial dates to numeric format

deepakg77717 — Mon, 29 Mar 2021 16:47:34 GMT

Thank you for your reply ..Reeza..

Re: Convert dates including partial dates to numeric format

deepakg77717 — Mon, 29 Mar 2021 16:57:35 GMT

Thank you again Paige Miller...

Re: Convert dates including partial dates to numeric format

mkeintz — Mon, 29 Mar 2021 23:27:23 GMT

I would differ with the advice to keep the data as character variables. Instead I suggest making 3 numeric variables: year, month, and day-of-month. Sometimes the day-of-month or month variable will be missing.

You can then create regular sas date values, imputing 1 for missing day-of-month, and 1 for missing month. This will support doing calculations and sorting using these (partly imputed, partly actual) date values. And you can check for missing day-of-month or missing month to determine whether the date in hand is from complete or partial date components.

Re: Convert dates including partial dates to numeric format

PaigeMiller — Tue, 30 Mar 2021 10:37:37 GMT

Yes, I agree with you @mkeintz that this is a better solution, but I asked the original poster specifically if the dates had to be maintained in their current form, where a missing day and month should be represented as 2021, and (s)he said yes. I suspect that after a short period of time trying to work with the character representation of full dates plus partial dates, (s)he will discover all the disadvantages that you point out, and then will make the decision to convert these to numeric.