topic Re: Convert character to numeric then to date in SAS Data Management

Convert character to numeric then to date

KPCklebspn — Wed, 27 Mar 2019 14:49:42 GMT

I've got a variable whose values are either blank or a 5 digit number that SAS is reading as a character. When I do proc contents for this var, tells me it's type char, format $5. This 5 digit number should actually correspond to a date, so I want to convert it to numeric then I can format it as a date.

What I've tried to do:

data new; set old; numVar=input(charVar, 5.); run;

If I do proc contents of the new dataset, it tells me that it's still a character var with len 5. Format/informat are blank. What am I doing wrong??

Thank you!

Re: Convert character to numeric then to date

PeterClemmensen — Wed, 27 Mar 2019 14:55:45 GMT

Can you provide an example of the character variable value that you want to convert?

Re: Convert character to numeric then to date

KPCklebspn — Wed, 27 Mar 2019 14:57:04 GMT

I've attached the proc freq of the charvar

Re: Convert character to numeric then to date

KPCklebspn — Wed, 27 Mar 2019 14:57:05 GMT

I've attached the proc freq of the charvar

Re: Convert character to numeric then to date

PeterClemmensen — Wed, 27 Mar 2019 15:01:20 GMT

Do you know what date the character value eg '42062' is to be interpreted as?

Re: Convert character to numeric then to date

KPCklebspn — Wed, 27 Mar 2019 15:04:26 GMT

27Feb2015

Re: Convert character to numeric then to date

TomKari — Wed, 27 Mar 2019 15:04:31 GMT

Your code works perfectly for me:

data old;
	charVar = '21635';
	output;
run;

proc sql;
	select * from dictionary.columns where libname="WORK" and memname="OLD";
run;

data new;
	set old;
	numVar=input(charVar, 5.);
run;

proc sql;
	select * from dictionary.columns where libname="WORK" and memname="NEW";
run;

                                                                                           Wednesday, 27 March, 2019 10:53:00 AM   1

                                                                                                                      Column
Library                                     Member                                      Column    Column    Column    Number
Name      Member Name                       Type      Column Name                       Type      Length  Position  in Table
                                                                                                                           Column
                                                                                                                           Index
Column Label         Column Format                                      Column Informat                                    Type
Order in
     Key  Extended      Not
Sequence  Type          NULL?  Precision     Scale  Transcoded?
------------------------------------------------------------------------------------------------------------------------------------
WORK      OLD                               DATA      charVar                           char           5         0         1
                                                                                                                                    
       0  char          no             0         .  yes        
                                                                                           Wednesday, 27 March, 2019 10:53:00 AM   2

                                                                                                                      Column
Library                                     Member                                      Column    Column    Column    Number
Name      Member Name                       Type      Column Name                       Type      Length  Position  in Table
                                                                                                                           Column
                                                                                                                           Index
Column Label         Column Format                                      Column Informat                                    Type
Order in
     Key  Extended      Not
Sequence  Type          NULL?  Precision     Scale  Transcoded?
------------------------------------------------------------------------------------------------------------------------------------
WORK      NEW                               DATA      charVar                           char           5         8         1
                                                                                                                                    
       0  char          no             0         .  yes        

WORK      NEW                               DATA      numVar                            num            8         0         2
                                                                                                                                    
       0  num           no             0         .  yes

Re: Convert character to numeric then to date

ballardw — Wed, 27 Mar 2019 15:44:34 GMT

@KPCklebspn wrote:

I've attached the proc freq of the charvar

Looks like a typical Excel date to SAS date conversion. If your data started in Excel then the issue is how you brought the data into SAS, and possibly what has been done since.

So, did this data start in Excel? If so please describe how you brought it into SAS.