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

I am running the following code (which is connecting to large data bases maintained by our IT unit on the 2008 MS Server).

 

proc sql;

create table work.tab2

as select c.customerid format=$char9.,a.casenumber format=$char9.

,c.datebegin_14 format=date12., c.statuscase format=$char9., duration_14 format=best5.

from rimsrpt.rptcasesummary c

left join rimsrpt.rptauthorization as a on c.extractdate=a.extractdate

and c.customerid=a.customerid

and c.casenumber=a.casenumber

where c.extractdate = "31Dec2016"d

and c.datebegin_at_clo IS NULL

and datebegin_14 >= "01Jan2016"d;

quit;

 

everything works fine except the field datebegin_14. This field returns only asterisks rather than the actual data in the table. It is stored on our server as a datetime field, but it uses NULL to reflect missing values rather than the . SAS uses. I tried using a string format for it ($char9.) but that returns an error rather than just not reporting the correct results. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

What returns if you remove the format entirely?

 

If it's a datetime, you want a datetime format, not a date format.

format=datetime. would be another option to try.

View solution in original post

6 REPLIES 6
Reeza
Super User

What returns if you remove the format entirely?

 

If it's a datetime, you want a datetime format, not a date format.

format=datetime. would be another option to try.

ChrisHemedinger
Community Manager

And make sure the database is giving you a SAS datetime (via the SAS/ACCESS engine) and not a native Microsoft datetime, which is different (measured in "ticks" and not in seconds).

 

If needed, you can convert using this technique.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Astounding
PROC Star

If datebegin_14 is a datetime, you will be selecting many more records than you would anticipate when using:

 

and datebegin_14 >= "01Jan2016"d;

 

If your database permits, you could try:

 

and datepart(datebegin_14) >= "01Jan2016"d;

 

Similar considerations would apply to extractdate, if that is a datetime.  Datetime values are measured in seconds, while dates are measured in days, so datetime values are much larger than date values.

 

SAS displays formatted values as a series of asterisks when the value is too large to fit into the assigned format.  You will need to assign a datetime format to datebegin_14, and not a date format.  NULL values are not even being extracted, since they fail on the WHERE conditions ... so they are not the issue.

noetsi
Obsidian | Level 7

I am new here so the responses came in faster than I could read them. I wanted to thank everyone for responding.

 

I used

c.datebegin_14 format=datetime and the results showed up.

 

extractdate used in the filter is listed as a datetime on the server, but actually only stored as a date. The way I filtered for it in the where statement is (other than a different SAS format) exactly how we do it the TQL used on the server. I am checking if the number of records in the server equal what SAS is pulling in.

 

This is the first time I have accessed the tables directly in SAS (until recently it was too slow to do this. We ran the data in the MS Server than exported it into SAS through excel which was very much less than ideal, but neccessary because we did not have the correct SAS ACCESS software to use ODBC until this week).

 

I mainly do statistical work in SAS so I am still learning PROC SQL which varies in important ways from the TSQL I know. 

ballardw
Super User

SAS displays all **** when the length or display space is insufficient to display the value. Since you are connecting to and MS SQL server it is very likely that the "date" variables actually are datetimes which convert when brought to SAS. SAS dates are numbers of days and datetimes are numbers of seconds.

If you only want the date portion of the data then use the DATEPART function: Datepart(c.datebegin_1) as datebegin


data junk;
    /* x is a datetime variable*/
   x = '01JAN2017:00:00:00'dt;
   put "x displayed with a datetime format" x= datetime20.;
   put "x displayed with a date format" x= date9.;
   put "x displayed with a numeric format" x= best20.;
   y=datepart(x);
   put 'y is a date:' y= date9.;
run;

The confusion on the part of many database folks between dates and datetimes is pet peeve of mine.

 

noetsi
Obsidian | Level 7
One thing I don't understand about the filter. Extractdate while stored as a datetime field actually has only dates (the time element is all 0's). Datebegin_14 is a datetime field, that is it has hours etc.

When I run
where c.extractdate = "31Dec2016"d

and c.datebegin_at_clo IS NULL

and datebegin_14 >= "01Jan2016"d;

I get exactly the same number of records as I get when I run

where EXTRACTDATE ='12/31/2016'
and DATEBEGIN_AT_CLO IS NULL
and DATEBEGIN_14 >= '01/01/2016'

in TSQL in the MS Server. Using datepart(datebegin_14) >= "01Jan2016"d; in SAS returns no values at all so my server must not support that.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1440 views
  • 1 like
  • 5 in conversation