BookmarkSubscribeRSS Feed
VRPotluri
Calcite | Level 5

Hi,

I am working on on SQL Server Database, with a low date as 0001-01-01, While reading this data into SAS these values are shown a nulls or ***** can any one of you please help on this. One millions thanks well in advance.

 

WIth regards

 

Ramana Potluri

6 REPLIES 6
Ksharp
Super User
***  usually because length of format is too short, try assign a longer format 
Like format date date9.

Or remove that format and see what is going on in it .
proc print ;
format date;


VRPotluri
Calcite | Level 5

I have already tried this. This did not work.

My problem here any date prior to 1958-01-01 is reading as missing. How can I resolve this.

Ksharp
Super User
Maybe you could import these date into SAS as character (at SQL Server side)
and transform it into data by INPUT() .


SASKiwi
PROC Star

What is the column type in SQL Server? Datetime or something else? How are you reading SQL Server? SAS/ACCESS to SQL Server or ODBC?

Kurt_Bremser
Super User

@VRPotluri wrote:

I have already tried this. This did not work.

My problem here any date prior to 1958-01-01 is reading as missing. How can I resolve this.


That would be very strange, as SAS accepts dates back to the 16th century:

dateval = input(datestring,yymmdd10.);
format dateval yymmddd10.;
cards;
1960-01-01
1958-01-01
1850-01-01
1750-01-01
1690-01-01
1590-01-01
;
run;

proc print;
run;

Result:

Obs    datestring       dateval

 1     1960-01-01    1960-01-01
 2     1958-01-01    1958-01-01
 3     1850-01-01    1850-01-01
 4     1750-01-01    1750-01-01
 5     1690-01-01    1690-01-01
 6     1590-01-01    1590-01-01
ballardw
Super User

Since I doubt that you have any actual values recorded for 0001-01-01 I suspect that exact value is being used as either a place holder or indicator for missing or invalid data. Due to vagaries of history and inconsistent calendars SAS does not support dates prior to the English adoption of the Gregorian calendar.

 

Also if you are looking at OUTPUT in Excel by any chance, Excel doesn't support dates prior to 1900.

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
  • 6 replies
  • 1298 views
  • 0 likes
  • 5 in conversation