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

I imported an excel datasheet using proc import and one of the date columns came in as a character variable with numbers populated. Is there anyway to modify the proc import so that the date data comes in as it should? MM/DD/YYYY? I know this is happening because some values are stored as year only and some are blank.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
No, and it wouldn't correctly handle the cases where you have a year only.
If you can read it in from a text file you can control the format, but not Excel.

You can use INPUT to convert the dates that are full dates but you'll need to handle the years only or missing separately.


if length(date) = 10 then new_date = input(date, mmddyy10.);
else if length(date)=4 then new_date = mdy(7, 1, input(date, 4.));
else if missing(date) then new_date = .;


View solution in original post

2 REPLIES 2
Reeza
Super User
No, and it wouldn't correctly handle the cases where you have a year only.
If you can read it in from a text file you can control the format, but not Excel.

You can use INPUT to convert the dates that are full dates but you'll need to handle the years only or missing separately.


if length(date) = 10 then new_date = input(date, mmddyy10.);
else if length(date)=4 then new_date = mdy(7, 1, input(date, 4.));
else if missing(date) then new_date = .;


Tom
Super User Tom
Super User

A variable only has one type, unlike a spreadsheet in a dataset you cannot mix numbers and character strings.  If the column has some cells with dates (numbers) and some with strings then SAS will be forced to create the variable as character. When it does that the values of cells that contain date values are stored as the string of digits that represent the number Excel stores for that date.  So the date 01JAN2021 will show up as the string '44197' instead of either '01JAN2021' or the number 22,281 that SAS would use to represent that date.

 

The best solution is to fix the column in the spreadsheet so that every value is a string.  Then what you have in SAS will be the string.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 2649 views
  • 1 like
  • 3 in conversation