BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASuserlot
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
jimbarbour
Meteorite | Level 14

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:

jimbarbour_0-1602166814891.png

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

View solution in original post

23 REPLIES 23
jimbarbour
Meteorite | Level 14

Try:

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

Jim

SASuserlot
Barite | Level 11
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
Meteorite | Level 14

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
Barite | Level 11

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

SASuserlot_0-1602126363329.png

 

jimbarbour
Meteorite | Level 14

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.

jimbarbour_0-1602132885849.png

 

Jim

 

SASuserlot
Barite | Level 11

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

SASuserlot_0-1602162151711.png

 

want to replace"." with "Missing"

SASuserlot
Barite | Level 11
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
Meteorite | Level 14

@SASuserlot,

 

You cannot do this:

jimbarbour_0-1602164539713.png

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
Barite | Level 11

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
Meteorite | Level 14

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.

 

jimbarbour_0-1602165872387.png

 

Jim

SASuserlot
Barite | Level 11

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
Meteorite | Level 14

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:

jimbarbour_0-1602166814891.png

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
Barite | Level 11
I really appreciate your effort to clarify this to me. I will try with your suggestion, and will let you know. really Thanks you

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 23 replies
  • 2055 views
  • 5 likes
  • 4 in conversation