topic Re: convert number to date error in SAS Programming

convert number to date error

Phil_NZ — Sat, 13 Feb 2021 10:40:31 GMT

Hi all SAS Users,

Today I tried to convert from number (automatically transported from excel to SAS) back to the date.

Shortly, I want to change from

Type           BDATE
134495         34624
134625         34325
13811D         37432
149482         34701

Type         BDATE
134495     17/10/1994
134625     22/12/1993
13811D     25/06/2002
149482     2/01/1995

The code is as below

data ARGENTINA_merge2_(drop=NAME BDATE);  
 set ARGENTINA_merge2;  
SASDATE = input(BDATE,32.)+'30DEC1899'd ; 
put SASDATE= yymmdd10.; 
 yr=input(year, ?? 32.); 
 run;

However, SAS inform this error message:

NOTE: Invalid argument to function INPUT

Actually I follow this post to form the conversion of SASDATE, but do not understand why it does not work this time.

Warm regards.

Re: convert number to date error

Kurt_Bremser — Sat, 13 Feb 2021 11:42:47 GMT

ALWAYS post the complete log of a step.

Re: convert number to date error

Shmuel — Sat, 13 Feb 2021 11:45:57 GMT

heck next code and adapt your program:

data _null_;
 xldt_delta = '30dec1899'd;
 dt=34624 + xldt_delta; put dt= ddmmyy10.;
run;

Re: convert number to date error

Ksharp — Sat, 13 Feb 2021 12:12:57 GMT

SASDATE = input(BDATE,32.)+'30DEC1899'd ;
-->
SASDATE = BDATE+'30DEC1899'd ;

BDATE is numeric not character type .

Re: convert number to date error

Phil_NZ — Sat, 13 Feb 2021 18:56:41 GMT

Hi @Ksharp

The data is numeric, not character, because when I change the code to what you mentioned, the error log as below appears

data ARGENTINA_merge2_(drop=NAME BDATE);   
  set ARGENTINA_merge2;  
  SASDATE = BDATE +'30DEC1899'd ; 
  format SASDATE yymmdd10.;  
  yr=input(year, ?? 32.);  
run;

Log

134       +               data ARGENTINA_merge2_(drop=NAME BDATE);   set ARGENTINA_merge2;  SASDATE = BDATE +'30DEC1899'd ;  format 
SASDATE yymmdd10.;  yr=input(year, ?? 32.);  run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      134:92   
NOTE: Invalid numeric data, BDATE='NA' , at line 134 column 92.

Warm regards!

Re: convert number to date error

Phil_NZ — Sat, 13 Feb 2021 19:07:09 GMT

Hi @Shmuel

While I put a code like that into my code

 data &outfm.merge2_(drop=NAME BDATE);
 	set &outfm.merge2;
    xldt_delta = '30dec1899'd;
    dt=34624 + xldt_delta; put dt= ddmmyy10.;

 run;

It seems to work, but there are two issues here:

1. It shows a long list of date in the log

dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994
dt=17/10/1994

2. When it shows the dt in the log correctly, in the output, it does not show like that

Could you please help me to sort it out?

Warmest regards.

Re: convert number to date error

Shmuel — Sat, 13 Feb 2021 19:20:47 GMT

@Phil_NZ, did you not notice that the literal number 34624 is just the first BDAT value in your data set? Replace the number with the appropriate data variable name.

Corresponding with @Ksharp I see you got a message:

Invalid numeric data, BDATE='NA' , at line 134 column 92.

It seems that BDAT is a char type? What does NA mean for you?

Re: convert number to date error

Phil_NZ — Sat, 13 Feb 2021 19:32:53 GMT

Hi @Shmuel and @Ksharp

Sorry it is my fault, yes, I just run proc contents and yes BDATE is a character variable.

And @Shmuel , could you please hint me what do you mean by replacing 34624 by "appropriate variable name" ? I did not get the idea this time then...

Many thanks and warm regards.

Re: convert number to date error

Shmuel — Sat, 13 Feb 2021 21:45:38 GMT

@Phil_NZ wrote:

Hi @Shmuel and @Ksharp

Sorry it is my fault, yes, I just run proc contents and yes BDATE is a character variable.

And @Shmuel , could you please hint me what do you mean by replacing 34624 by "appropriate variable name" ? I did not get the idea this time then...

Many thanks and warm regards.

I asked to adapt your code and this is what you ran:

data &outfm.merge2_(drop=NAME BDATE);
 	set &outfm.merge2;
    xldt_delta = '30dec1899'd;
    dt=34624 + xldt_delta; put dt= ddmmyy10.;
 run;

As BDATE is char type, the adapted code should be:

data &outfm.merge2_(drop=NAME BDATE);
 	set &outfm.merge2;
    xldt_delta = '30dec1899'd;
	if upcase(bdate) ne 'NA' and not missing(bdate)
	then dt=input(bdate,?? <bdate informat>) + xldt_delta;
	put dt= ddmmyy10.; /* mark line for production */
    format dt ddmmyy10.;
 run;

if bdate contains number like 34624 then the informat should be 5.