Convert numeric values into characters while using PROC SQL INSERT INTO

[Dubro]

Dear Group,

I want to read values from a MySQL Dump which are of type BIGINT(20)
(16-bit values). Is there a possibility to silently convert values from
numeric to character while reading values from an INSERT INTO
statement?

See example:
proc sql;
create table log_visits
(
logid num, userid char(20), logType char(20),
ip num, channelID num, keywordID num, keyword char(256),
keyValue num, shopID num, refinfo char(256), referer char(256), time
char(20)
);
quit;

proc sql;
insert into log_visits
values
(4879671487765984868,'4879671487765984868','click',1357166180,2,2280453,'',0,0,'','http://www.Suche','20060101183733')
values
(4879671459621714959,'4879668968540683279','click',3277667343,2,1347872,'',1,0,'','','20060101183726')
values
(4879671427701090352,'4879671427701090352','click',1421813808,2,2096098,'',1,0,'','','20060101183719');
quit;

The first value (LOGID) is numeric in the dump but should be read as
character by SAS.

*very desparated;

Dubro

[Richard A. DeVenezia]

Post by Dubro
Dear Group,
I want to read values from a MySQL Dump which are of type BIGINT(20)
(16-bit values). Is there a possibility to silently convert values
from numeric to character while reading values from an INSERT INTO
statement?
proc sql;
create table log_visits
(
logid num, userid char(20), logType char(20),
ip num, channelID num, keywordID num, keyword char(256),
keyValue num, shopID num, refinfo char(256), referer char(256), time
char(20)
);
quit;
proc sql;
insert into log_visits
values

(4879671487765984868,'4879671487765984868','click',1357166180,2,2280453,'',0
,0,'','http://www.Suche','20060101183733')

Post by Dubro
values

(4879671459621714959,'4879668968540683279','click',3277667343,2,1347872,'',1
,0,'','','20060101183726')

Post by Dubro
values

(4879671427701090352,'4879671427701090352','click',1421813808,2,2096098,'',1
,0,'','','20060101183719');

Post by Dubro
quit;
The first value (LOGID) is numeric in the dump but should be read as
character by SAS.
*very desparated;
Dubro

You have shown us the MySQL code for creating the table.
What is the command used to 'Dump' the table to (presumably) an external
text file?
What does the dump look like? If there are dumping options to cause MySQL
to dump everything in ascii-mode (or if dump is ascii only) then I presume
SAS INPUT statements can read what should be a very well structured text
file.

So, can you show a couple of lines from the dump?

[Dubro]

Dear Richard,

well I'm struggeling with this dump for a while (see older postings).
You are right by assuming that the dump delivers a well structured
file. Usually there would be no problem to read this file. But then we
have a field called REFERER which can include ANY character (as it is a
referer possibly from a search engine). So there are records having an
apostrophe (' like in "Let's go") or parentheses (like in
+(DVD+PLAYER)). That's the reason I can't read my dump like e.g.:

data dsn;
infile dump dsd lrecl = 1048576;
input VARDEFINITIONS @@;
run;

The lrecl has maximum value because the MySQL dump has lines up to 1MB.
Using this code, SAS stumbles when the field REFERER has something like
('http:/sometest...Los+geht\'s'), because of the unbalanced quotes (If
I only could use %bquote ;-).

As my Dump includes Bulk inserts I have hundreds of records in one line
delimited by parenthesis. So I tried to preprocess the dump file:

data _null_;
length onerec $32767;
infile logvis dsd dlm = "()" lrecl = 1048576 obs = 3;
input onerec $ @@;
/* LRECL is set to maximum length of _input_ variable */
file outfile lrecl = 32767;
if index(onerec, "5c27"x) > 0 then onerec = tranwrd(onerec, "\'", "");
if onerec ne "," and onerec ne ";" then put onerec;
run;

Here the trouble is that some entries in the REFERER field go like
('http://XXX.SD-XS24+(DVD/HDD-Recorder)&spell=1') which gives me
another problem. This brought me to try to read the dump directly into
PROC SQL. Here (again) I found the problem of having BIGINT values.
PHHHHHHH...

In a few weeks (or so) it is planned to buy a license for SAS/ACCESS to
connect directly to MySQL. Until then I have to read the data directly
from the dumps.

So if anyone can give hints for a solution to this situation I would
appreciate that very much.

Kind regards.

Dubro

[Mikon]

Post by Dubro
Dear Richard,
well I'm struggeling with this dump for a while (see older postings).
You are right by assuming that the dump delivers a well structured
file. Usually there would be no problem to read this file. But then we
have a field called REFERER which can include ANY character (as it is a
referer possibly from a search engine). So there are records having an
apostrophe (' like in "Let's go") or parentheses (like in
data dsn;
infile dump dsd lrecl = 1048576;
run;
The lrecl has maximum value because the MySQL dump has lines up to 1MB.
Using this code, SAS stumbles when the field REFERER has something like
('http:/sometest...Los+geht\'s'), because of the unbalanced quotes (If
I only could use %bquote ;-).
As my Dump includes Bulk inserts I have hundreds of records in one line
data _null_;
length onerec $32767;
infile logvis dsd dlm = "()" lrecl = 1048576 obs = 3;
/* LRECL is set to maximum length of _input_ variable */
file outfile lrecl = 32767;
if index(onerec, "5c27"x) > 0 then onerec = tranwrd(onerec, "\'", "");
if onerec ne "," and onerec ne ";" then put onerec;
run;
Here the trouble is that some entries in the REFERER field go like
('http://XXX.SD-XS24+(DVD/HDD-Recorder)&spell=1') which gives me
another problem. This brought me to try to read the dump directly into
PROC SQL. Here (again) I found the problem of having BIGINT values.
PHHHHHHH...
In a few weeks (or so) it is planned to buy a license for SAS/ACCESS to
connect directly to MySQL. Until then I have to read the data directly
from the dumps.
So if anyone can give hints for a solution to this situation I would
appreciate that very much.
Kind regards.
Dubro

Hi

If i understand correctly, MySql just takes value of field (which could
contain any character), adds apostrophes around it and writes it to the dump
without any changes (like doubling apostrophes). If it is so, then, i'm
afraid, nothing can be done in principle. Are there any other means of
exporting data from MySQL? I have some ideas:
1) Export data through OLEDB/ODBC to text file with script or external
program and then feed this file to SAS.
2) Make a view in MySQL which doubles all apostrophes in REFERER and dump
that view.

[Dubro]

Hi,

As a special constraint it is also not possible to create the dump on
the same server where the MySQL DB resides. That means, that I can't
choose the delimiter or the quoting behaviour.
Now I resolved the issue by writing a PERL script. One aim of this
program was to convince my colleagues of SAS superiority in working
with data. THIS part of the exercise hasn't worked out. Anyway: there
are things to come.

Thank you for all hints and tips.

Dubro