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

[SASuserlot]

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

[jimbarbour]

Try:

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

Jim

[SASuserlot]

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.

[jimbarbour]

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

[SASuserlot]

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

[jimbarbour]

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

[SASuserlot]

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"

[Kurt_Bremser]

Please post the complete log of the step you ran.

[SASuserlot]

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

[jimbarbour]

@SASuserlot,

You cannot do this:

in SAS.  

1. c5 is numeric. 
2. You cannot set c5 to "[Missing]".  "[Missing]" is character and is not numeric.  You cannot put a non-numeric value into a numeric variable.
3. 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

[SASuserlot]

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!

[jimbarbour]

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

[SASuserlot]

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

[jimbarbour · Accepted solution]

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:

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

Jim

[SASuserlot]

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