BookmarkSubscribeRSS Feed
deepakg77717
Calcite | Level 5

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.

 

 

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
deepakg77717
Calcite | Level 5

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
Diamond | Level 26

@deepakg77717 wrote:

 

Could you please elaborate your suggestion. 


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

--
Paige Miller
deepakg77717
Calcite | Level 5

Thank you again Paige Miller...

Reeza
Super User

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
Calcite | Level 5

Thank you for your reply ..Reeza..

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 7865 views
  • 4 likes
  • 4 in conversation