topic Re: how to convert date 2020-10-12( numeric) to 2020-10-12( character) in SAS Programming

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

SASuserlot — Thu, 08 Oct 2020 01:54:21 GMT

how to convert numeric date to character date and display same way as mentioned above. I need it because there are missing dates where I need place "Missing" which is character

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

jimbarbour — Thu, 08 Oct 2020 15:13:14 GMT

Try:

IF    MISSING(Numeric_Date)  THEN
    Character_Date = 'Missing';
ELSE
    Character_Date = Put(Numeric_Date, mmddyyd10.);

Jim

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

SASuserlot — Thu, 08 Oct 2020 02:38:16 GMT

c5=input(strtd ,32.)+ '30dec1899'd;/* excel has the character so the date converted to charcter "days"*/
format c5 yymmdd10.;										/*changing back to numeric and date 11*/

if missing(c5) then c5="[Missing]";
else c5=put(c5,mmddyyd10.);

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      9276:24   9277:9
NOTE: Invalid numeric data, '09-27-2012' , at line 9277 column 9.

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

jimbarbour — Thu, 08 Oct 2020 02:47:12 GMT

You can't use c5 for numeric and character purposes.

This cannot work:

c5=input(strtd ,32.)+ '30dec1899'd;/* excel has the character so the date converted to charcter "days"*/
format c5 yymmdd10.;										/*changing back to numeric and date 11*/

if missing(c5) then c5="[Missing]";
else c5=put(c5,mmddyyd10.);

You have to have a character variable to contain things like "[Missing]"

c5=input(strtd ,32.)+ '30dec1899'd;/* excel has the character so the date converted to charcter "days"*/
format c5 yymmdd10.;										/*changing back to numeric and date 11*/

if missing(c5) then c5_char ="[Missing]";
else c5_char = put(c5, mmddyyd10.);

Jim

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

SASuserlot — Thu, 08 Oct 2020 03:06:14 GMT

understand is there any possible way I can do the display like this

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

ballardw — Thu, 08 Oct 2020 04:04:31 GMT

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

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

jimbarbour — Thu, 08 Oct 2020 04:55:14 GMT

Yes, this if statement:

	IF	missing(c5) then 
		c5_char ='"Missing"';
	else
		c5_char = put(c5, yymmdd10.);

Will produce the output you requested. I just ran it now.

Jim

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

Kurt_Bremser — Thu, 08 Oct 2020 06:16:25 GMT

Use a custom format, as @ballardw suggested.

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

SASuserlot — Thu, 08 Oct 2020 13:02:41 GMT

I tried it but it still says invalid numeric data for some reason. this is the column attributes in my dataset. row 8, 9 have missing values ".". where as row 10 has the date in ddmmyy10. I

want to replace"." with "Missing"

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

Kurt_Bremser — Thu, 08 Oct 2020 13:08:06 GMT

Please post the complete log of the step you ran.

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

SASuserlot — Thu, 08 Oct 2020 13:24:03 GMT

454
2455  c5=input(strtd ,32.)+ '30dec1899'd;/* excel has the character so the date converted to charcter
2455! "days"*/
2456  format c5 yymmdd10.;                                        /*changing back to numeric and date
2456! 11*/
2457
2458  if missing(c5) then c5="[Missing]";
2459  else c5=put(c5,mmddyyd10.);
2460
2461  run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      2458:24   2459:9
NOTE: Variable C6 is uninitialized.
NOTE: Variable C7 is uninitialized.
NOTE: Invalid numeric data, '09-27-2012' , at line 2459 column 9.

please ignore c6, c7 notes, I looking for c5 which has the date

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

jimbarbour — Thu, 08 Oct 2020 13:56:03 GMT

@SASuserlot,

You cannot do this:

in SAS.

c5 is numeric.
You cannot set c5 to "[Missing]". "[Missing]" is character and is not numeric. You cannot put a non-numeric value into a numeric variable.
The result of put(c5, mmddyyd10.) is character. You cannot put a non-numeric value into a numeric variable.

You cannot do this in SAS. This will never work in 10,000,000 years.

You must use two variables. One variable should be numeric. The other variable should be character. If c5 is to be your final output and c5 needs to contain character values, then c5 must be a character variable.

You must therefore NOT use c5 to hold the numeric date values that you have. You have to use some other variable. c5_temp might be a good name.

If you have:

c5_temp (a numeric variable)

c5 (a character variable)

then you can do what you are trying to do.

Your code would then look like this:

If MISSING(C5_Temp) then 
    c5 = "[Missing]";
else
    c5 = put(c5_temp, mmddyyd10.);

Jim

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

SASuserlot — Thu, 08 Oct 2020 13:59:47 GMT

Yes , I totally agree with it, but can i create excel column where it contains dates and if date missing put a note of "missing" is it possible at all? because the problem here is they both have to display in the same column so I believe they have to be in same variable in dataset!

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

jimbarbour — Thu, 08 Oct 2020 14:05:20 GMT

First, try running the code. What do you get in Excel? Excel should be able to interpret something like 2019-09-12 as a date.

Jim

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

SASuserlot — Thu, 08 Oct 2020 14:07:20 GMT

I ran excel giving the date as you mentioned. But where ever the date missing it display ".". this is where I want "Missing" word.

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

jimbarbour — Thu, 08 Oct 2020 14:25:07 GMT

Show me your code. I think you're still using a numeric variable for column 5. YOU CANNOT USE A NUMERIC VARIABLE to represent c5. c5 must be character.

Let me show you some code. Please notice that c5 is character. C5 must be defined as character for it to contain "Missing" as a value.

DATA	Date_Data;
	INPUT	c5_Temp	:	ANYDTDTE10.;

DATALINES;
2019-09-12
2019-09-13
2019-09-14
2019-09-15
2019-09-16
.
.
2019-09-19
;
RUN;

DATA	Excel_Data;
	LENGTH	C5	$10;
	DROP	C5_Temp;
	SET	Date_Data;

	IF	missing(c5_Temp) then 
		c5 ='"Missing"';
	else
		c5 = put(c5_temp, yymmdd10.);
RUN;

Here are my results in Excel:

Please notice that c5 contains the values "Missing" just as you requested in 6th and 7th rows.

Please notice also that if I add +1 to the value in the first column, I get a number. A number means that Excel is interpreting the value in C5 as a date.

Then, look at the third column. The third column is identical to the second column, but I have formatted the value in Excel. Notice that the value is one day (+1) greater than the date in the first column.

Everything is working just as you want, but:

You must use two variables. One must be numeric. The other must be character.
The variable c5 must be a character variable. If you want c5 to hold values like "Missing", then c5 must be character.

Jim

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

SASuserlot — Thu, 08 Oct 2020 14:30:06 GMT

I really appreciate your effort to clarify this to me. I will try with your suggestion, and will let you know. really Thanks you

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

SASuserlot — Thu, 08 Oct 2020 14:43:15 GMT

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

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

jimbarbour — Thu, 08 Oct 2020 14:50:10 GMT

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

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

SASuserlot — Thu, 08 Oct 2020 15:09:43 GMT

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