About tagsets.sql

[Stephane]

Hi,

a little question about the tagset SQL.

I used it but all my numeric fields with a format = 20.3 are understood like varchar(20). Is there any reason ?

The problem is the missing values.

I modified the tagset to have
do /if cmp(_value_, '.');
put 'NULL';

But with these varchar fields, it doesn't understand the dot alone because the value is a dot quoted : '.'
I tried to put
do /if cmp(_value_, "'.'");
put 'NULL';

or this :
do /if cmp(_value_, ''.'');
put 'NULL';

It doesn't work.

Any idea ?

Stephane.

Message was edited by: Stephane Message was edited by: Stephane

[David_SAS]

Stephane,

I'm not aware of an SQL tagset template. There is an SQL table template, which is something completely different. Tagset and table templates have different languages and behave differently in ODS.

-- David Kelley, SAS

[David_SAS]

A coworker helpfully pointed out to me that there is an SQL tagset:

http://support.sas.com/rnd/base/topics/odsmarkup/sql.html

-- David Kelley, SAS

[Stephane]

David,

this is the tagset that I'm talking about.

[Eric_SAS]

Hello Stephane,

The colspec entry event catches the type and width of each variable. I'm afraid it's not
very smart. Or worse, the procedure is not so good about telling the true type of the
column.

In some of the other tagsets, CSV, and Excelxp we use a regular expression to determine
if the variable is a number. We could lift that code and use it here. To see what is going on
you could add this to the colspec event just so you would know what the tagset was getting
for information about each column.

putlog "Name: " name " Type: " type " Width: " width;

That can help us figure out if there is anything else we can do. In SAS 9.2 this information is much richer and consistent across all procs.

[Stephane]

Hi Eric,

thank you, I'll investigate.



Ok, here is the partial putlog :

Name: id_customer Type: string Width: 11
...
Name: smt_patr_pc_m_1 Type: string Width: 20
Name: smt_rvnu_pc_m_1 Type: string Width: 20
...

smt_patr_pc_m_1 has attributes from proc contents :
Length=8, format=20.3 informat=20.3

The first values of smt_patr_pc_m_1 is 153.000, 253.000 ... no commas.

I didn't see that id_customer was saved as string. It's a numeric field also. Message was edited by: Stephane