topic Re: SQL-Date in SAS in SAS Procedures

SQL-Date in SAS

ren2010 — Mon, 25 Jan 2010 17:38:44 GMT

Hi,

I am creating a table and populate a datetime field with null value,how can i do this.
i tried using put,but gives me lots of error.

any suggestions....

Ren

Re: SQL-Date in SAS

sbb — Mon, 25 Jan 2010 17:53:08 GMT

Share what SAS code you have tried (unsuccessfully) for a proper response from others on this forum.

Scott Barry
SBBWorks, Inc.

Re: SQL-Date in SAS

ren2010 — Mon, 25 Jan 2010 17:59:18 GMT

proc sql;
create table test1 as
select id ,put(.,datetime19.) into dttime;
quit;

The value in dttime in normal case ,for eg:01DEC2005:00:00:00
but i want null value in there instead of 01DEC2005:00:00:00

Re: SQL-Date in SAS

DanielSantos — Mon, 25 Jan 2010 18:00:13 GMT

No NULLS ins SAS!

Although some defend that NULLS and MISSING are conceptually the same, they do represent two different things.

From my understanding:

NULL value should mean, NO VALUE FOR
MISSING value should mean, NO KNOWN VALUE FOR

First assumes that some particular information has no value for some attribute, the second assumes that there is no information. Clearly two different things for data analysis and statistics.

Another difference is that a MISSING value represents actually a numeric value, it evaluates to - infinite. NULLS are just NULLS and can only be compared as a match to another NULL value or not.

Anyway, SAS will translate MISSINGS to NULLS when loading data to a RDBMS.

Being a datetime a numeric value, just use the missing notation (represented by . ) like this:
[pre]
select . as DT format=datetime30.6 ...
[/pre]
Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: SQL-Date in SAS

ren2010 — Mon, 25 Jan 2010 18:04:48 GMT

I have a create a new sas dataset with null value for datetime field
so when using Sql,can i use put(.)?

Please help

Re: SQL-Date in SAS

ren2010 — Mon, 25 Jan 2010 18:10:26 GMT

I got the following error;

proc sql;
2518 create table test1 as
2519 select id ,. as te format=datetime19.;
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, LENGTH,
TRANSCODE.

ERROR 76-322: Syntax error, statement will be ignored.

Re: SQL-Date in SAS

sbb — Mon, 25 Jan 2010 19:11:33 GMT

Here is a place to start - PROC SQL documentation.

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search argument for this topic/post:

proc sql assign missing value site:sas.com

SAS SQL Procedure User's Guide
http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001340013.htm

Re: SQL-Date in SAS

Flip — Mon, 25 Jan 2010 19:34:55 GMT

I execute that code and get no errors. Check spellings arouond that point in your code.