topic Re: About tagsets.sql in ODS and Base Reporting

About tagsets.sql

Stephane — Tue, 29 May 2007 15:56:25 GMT

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

Re: About tagsets.sql

David_SAS — Tue, 29 May 2007 16:29:54 GMT

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

Re: About tagsets.sql

David_SAS — Tue, 29 May 2007 17:11:23 GMT

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

Re: About tagsets.sql

Stephane — Tue, 29 May 2007 18:46:12 GMT

David,

this is the tagset that I'm talking about.

Re: About tagsets.sql

Eric_SAS — Tue, 29 May 2007 22:40:38 GMT

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.

Re: About tagsets.sql

Stephane — Wed, 30 May 2007 09:21:16 GMT

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