convert number to date error

[Phil_NZ]

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

to

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.

[Kurt_Bremser]

ALWAYS post the complete log of a step.

[Shmuel]

heck next code and adapt your program:

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

[Phil_NZ]

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.

[Shmuel]

@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?

[Phil_NZ]

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.

[Shmuel · Accepted solution]

---

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

[Ksharp]

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

BDATE is numeric not character type .

[Phil_NZ]

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!