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

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.

 

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

View solution in original post

8 REPLIES 8
Shmuel
Garnet | Level 18

heck next code and adapt your program:

data _null_;
 xldt_delta = '30dec1899'd;
 dt=34624 + xldt_delta; put dt= ddmmyy10.;
run;
Phil_NZ
Barite | Level 11

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

My97_0-1613243203316.png

 

Could you please help me to sort it out?

 

Warmest regards.

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
Shmuel
Garnet | Level 18

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

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.

 

 

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
Shmuel
Garnet | Level 18

@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
Super User
SASDATE = input(BDATE,32.)+'30DEC1899'd ;
-->
SASDATE = BDATE+'30DEC1899'd ;

BDATE is numeric not character type .
Phil_NZ
Barite | Level 11

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!

Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.

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
  • 8 replies
  • 734 views
  • 2 likes
  • 4 in conversation