Convert dates including partial dates to numeric format

[deepakg77717]

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.

[PaigeMiller]

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.

[deepakg77717]

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 ....

[PaigeMiller]

---

@deepakg77717 wrote:

Could you please elaborate your suggestion. 

---

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

[deepakg77717]

Thank you again Paige Miller...

[Reeza]

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&docsetTarge...

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.

---

[deepakg77717]

Thank you for your reply ..Reeza..

[mkeintz]

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.

[PaigeMiller]

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.