how to convert date 2020-10-12( numeric) to 2020-10-12( character)

[SASuserlot]

Question for you, how to do if the  dataset have the date variable in the following format ( numeric), because you date_data set in the anydtdte10. format but in my data already its in yymmdd10.

2019-09-12
2019-09-13
2019-09-14
2019-09-15
2019-09-16
.
.
2019-09-19

[jimbarbour]

ANDTDTE10. is not a FORMAT.  It is an INFORMAT.  SAS uses the informat to read the data.  The data is stored as numeric.  My data is identical to your data once it is inside SAS.  My data is numeric.

Here is my data:

So, in other words, the code that I gave you works with numeric data.  Notice also that the 6th and 7th values are a period (.) -- just like in your data.  A period represents a missing numeric value.

Jim

[SASuserlot]

Spoiler

Thanks, I ran your code, I am happy with that. l will try to achieve what yo achieved. thanks

[SASuserlot]

Look what I got😂🤩🤗. thanks a lot. it worked

[SASuserlot]

now I have to focus on darn color code😒🤩☺

[ballardw]

No need to "convert" anything.

Create a custom format and use that with the date variable(s).

Proc format;
value Mydate
low-high=[yymmddd10.]
. = "Missing"
;

data example;
   do date=., '01Jan2020'd to '05Jan2020'd;
      output;
   end;
   format date yymmddd10.;
run;

ods excel file="<path>\excelexample.xlsx";

proc print data=example;
   var date;
   format date mydate.;
run;
ods excel close;

The Proc format code creates a custom date format I have named mydate. The Low-High  indicates the range of values to display when not missing and the [yymmddd10.]  says to use the dash version of the date in yyyy-mm-dd format.The . says to display any missing values as the text in quotes.

The data step is just to create a small data set of values with one missing.

Then the Ods Excel to Ods excel close prints the data set to an XLSX file. If you run this code it is up to you to provide a path that is valid on your system.

This will have the actual dates as date values that Excel can use as normal and character value for the missing.

Note: the SAS date formats do not display any year past 9999 even though you can have values much later than that. The year portion will display as ****.

[jimbarbour]

@ballardw,

Is there a way to get the representation produced by the custom format to export to Excel?  In other words, can that format be used such that "Missing" shows up in Excel?

I know about something like:

Character_Date = PUT(Numeric_Date, MyDateFmt.);

But is there another way where the custom format can be applied such that "Missing" shows up in Excel without having to do a PUT on each variable?

Jim

[ballardw]

---

@jimbarbour wrote:

@ballardw,

Is there a way to get the representation produced by the custom format to export to Excel?  In other words, can that format be used such that "Missing" shows up in Excel?

I know about something like:

Character_Date = PUT(Numeric_Date, MyDateFmt.);

But is there another way where the custom format can be applied such that "Missing" shows up in Excel without having to do a PUT on each variable?

Jim

---

The example I showed used proc print and ODS EXCEL to accomplish this.

Since the OP did not specify how he is sending results to XLSX I'm not going to spend time testing every single one. I think I know at least 8 different ways to generate "Excel" output, not that I recommend some of them.

[Kurt_Bremser]

Use a custom format, as @ballardw suggested.