change date format after importing from Excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

change date format after importing from Excel

Hello,

I have date1 imported directly from Excel, and date1 have two types of value, for example: year 1990 and date-year-month 18May2005.

But in SAS, the year-month dates are changed to Excel date value (18May2005 is 38490), and year dates are the same. So I have 38490 and 1994 at the same time.

So for variable date1, I want to change the format of Excel date value to a regular date. I know that and it is date1= date1 - 21916;(Excel date value to SAS date value).

But I have an issue here, I cannot format date1 conditional on its value ( i.e if date1 <10000, it will be a year value, if date1 >10000, it will be an Excel date).

For example, when I format date1 as date9., 38490 becomes 18May2005, but 1990 also becomes 13Jun1965.

Is there a way I can do conditional formatting? Thank you!

p.s. I cannot change the 1990 to a SAS date value, because it can be 1Jan1990 or 1Dec1990.

      I even tried to have new variables like date2 and date3 conditional on the value of date1, but when I combined date2 and date3, 18May2005 became to SAS date value, and 1994 was still there.


Accepted Solutions
Solution
‎10-12-2014 02:54 PM
Super User
Super User
Posts: 6,851

Re: change date format after importing from Excel

SAS has two types of variables, fixed length character strings and floating point numbers.  If you attach a format to a variable so that the value is displayed in a more meaningful way then there is no way to tell it to use the format for only certain values. 

In SAS a date is stored as the number of days since 01JAN1960.  This is the type of values that the formats understand and that date manipulation functions understand.  In this form there is no way to store just a year.

You could define your own scheme for storing partial date values.

You could use more than one variable. Year and Date as you seem to have done.  Value and Format or Value and Informat might also work.

You could store them as character strings. You could store them as numbers, but don't expect SAS functions to understand them without first converting them to valid dates.

View solution in original post


All Replies
Super User
Posts: 11,144

Re: change date format after importing from Excel

No conditional formatting on a variable. A single variable in SAS may only have a single format associated.

If you want to have any SAS Date variables you will probably need to have 2 variables, one for the full dates and one for the single year values. However, do you have any valid dates in the 1960 to maybe 1970 range? It will be somewhat difficult to deal with them as numeric. You may need to force that date column to come in as text and do some manipulation to create 2 variables.

What did you try earlier with 2 variables? post the code.

You may also want to describe what you intend to do with these later.

Occasional Contributor
Posts: 9

Re: change date format after importing from Excel

Thank you! I am successful now, I create two variables, one for year and one for date. After I change the date-year-month to year, I manage to combine this new year variable with the original year variable.

Previously, I just wanted to have a single variable for date. Yes, I could have a year variable, but I also didn't want to lose information for the date-year-month variable when it is available.

Now I will keep these two variables.

Occasional Contributor
Posts: 9

Re: change date format after importing from Excel

Hi Ballardw,

I have a quick question. As you said "A single variable in SAS may only have a single format associated." So it is not possible to have a date variable with data Jan 2000 and 2000, right?  Thanks!

Solution
‎10-12-2014 02:54 PM
Super User
Super User
Posts: 6,851

Re: change date format after importing from Excel

SAS has two types of variables, fixed length character strings and floating point numbers.  If you attach a format to a variable so that the value is displayed in a more meaningful way then there is no way to tell it to use the format for only certain values. 

In SAS a date is stored as the number of days since 01JAN1960.  This is the type of values that the formats understand and that date manipulation functions understand.  In this form there is no way to store just a year.

You could define your own scheme for storing partial date values.

You could use more than one variable. Year and Date as you seem to have done.  Value and Format or Value and Informat might also work.

You could store them as character strings. You could store them as numbers, but don't expect SAS functions to understand them without first converting them to valid dates.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 879 views
  • 3 likes
  • 3 in conversation