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.
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.
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.
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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.