BookmarkSubscribeRSS Feed
Stephane
Quartz | Level 8
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
5 REPLIES 5
David_SAS
SAS Employee
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
SAS Employee
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
Quartz | Level 8
David,

this is the tagset that I'm talking about.
Eric_SAS
SAS Employee
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
Quartz | Level 8
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 827 views
  • 0 likes
  • 3 in conversation