topic Re: Problem with create table statement in SAS Enterprise Guide

Problem with create table statement

noetsi — Fri, 13 Jan 2017 20:55:35 GMT

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.

Re: Problem with create table statement

Reeza — Fri, 13 Jan 2017 20:57:50 GMT

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.

Re: Problem with create table statement

Astounding — Fri, 13 Jan 2017 21:06:35 GMT

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.

Re: Problem with create table statement

ChrisHemedinger — Fri, 13 Jan 2017 21:07:50 GMT

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.

Re: Problem with create table statement

ballardw — Fri, 13 Jan 2017 21:10:30 GMT

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.

Re: Problem with create table statement

noetsi — Fri, 13 Jan 2017 21:16:23 GMT

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.

Re: Problem with create table statement

noetsi — Fri, 13 Jan 2017 21:40:32 GMT

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.