BookmarkSubscribeRSS Feed
jcurran1289
Calcite | Level 5

Hi, 

I am having a problem with a variable that is has two different formats in excel, number and date. 

 

I have tried the following piece of code which helped me before with a problem i had

(Source: https://communities.sas.com/t5/Base-SAS-Programming/convert-variable-of-Char-and-numeric-to-date/m-p...)

 

if index(num(k),'/') >0 then num_form(k)=input(num(k), mmddyy10.);
else if num(k)~='missing' then num_form(k)= input(num(k), 8.);

 

i was planning to convert to a date then back to a number but it didnt work which you can see in the image i attached. i have also tried 

 

if index(num(k),'/') >0 then num_form(k)=input(num(k), 8..);
else if num(k)~='missing' then num_form(k)= input(num(k), 8.);

 

which leaves me with blanks where the dates were.

 

can someone please help??


sas_prob.PNG
3 REPLIES 3
ballardw
Super User

Is the variable that you have in SAS numeric or character when you read the data? If numeric then all the character stuff is likely gone.

 

I suspect that easiest would be to go back to Excel. Make sure the date column(s) are formatted as dates.

Do a file>save as> to a CSV file. Then use proc import or the import wizard to read the data. The the GUESSINROWS value to either 32000 or the number of rows in the file, which ever is smaller.

 

You have a much better chance of getting the values you want and as dates.

RahulG
Barite | Level 11

Excel consider 1Jan1900 as 0

SAS  consider 1Jan1960 as 0

 

So while converting date to number make use of following formula

SAS_date_time = (Excel_date_time - 21916)

Kurt_Bremser
Super User

Actually, the zero day in SAS is 01jan1960, while the zero day in Excel is really 30dec1899.

Excel displays the value 1 as 01jan1900, but this is wrong, as Excel (current version 2010) still considers 1900 to be a leap year (with a February 29 that never existed), a bug that is fixed in other office suites. This is also the reason why Excel can't work with negative values as dates, in contrast to LibreOffice et al.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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