Sql passthrough not displaying non ascii characters correctly

ScoobieR — Wed, 08 Nov 2023 23:19:38 GMT

I'm using the following code using a sql passthrough but it is not correctly displaying the non ascii characters - see code sample A. But when I run the same code using libnames (much much slower so not an option for me to use when looking at larger timeframes and more countries) the characters all display correctly - see sample code B. It feels I need an extra option or piece of code added - any ideas please from this wonderful group of SAS Whizz's would be very welcome. Thank you

Code A

proc sql;
connect to mysql as myconn (user=&user. password=&password. server=&server. port=3306 database=core);
create table memships as
select * from connection to myconn
(
select a.invoice_reference, a.invoice_date, a.period_date, a.id_invoice_type, a.id_invoice_status, a.to_id_user,
a.from_id_company, g.bu_name, a.total_amount, a.to_tax_reference, a.from_id_org,
a.to_id_person, a.to_company_name, a.to_person_name, a.to_address_line1, a.to_address_line2, a.to_address_state,
a.to_address_postcode, c.id_payment_status, d.email_address, e.email_address as email_1
from invoice as a
left outer join membership_application as b
on a.id_membership_application = b.id_membership_application
left outer join payment as c
on a.id_invoice = c.invoice_id_invoice
left outer join user as d
on a.to_id_user = d.id_user
left outer join person as e
on a.to_id_person = e.id_person
left outer join org_region as f
on a.from_id_org = f.id_region
left outer join company as g
on a.from_id_company = g.id_company
where date(period_date)>= '2023-10-01' and date(period_date) <= '2023-10-31' and f.id_country = 10987 and c.id_payment_status in (1,3)
and a.id_invoice_type in (1,2);
);
disconnect from myconn;
quit;

Code B

proc sql;
create table memships1 as
select a.invoice_reference, a.invoice_date, a.period_date, a.id_invoice_type, a.id_invoice_status, a.to_id_user,
a.from_id_company, g.bu_name, a.total_amount, a.to_tax_reference, a.from_id_org,
a.to_id_person, a.to_company_name, a.to_person_name, a.to_address_line1, a.to_address_line2, a.to_address_state,
a.to_address_postcode, c.id_payment_status, d.email_address, e.email_address as email_1
from core.invoice as a
left outer join core.membership_application as b
on a.id_membership_application = b.id_membership_application
left outer join core.payment as c
on a.id_invoice = c.invoice_id_invoice
left outer join core.user as d
on a.to_id_user = d.id_user
left outer join core.person as e
on a.to_id_person = e.id_person
left outer join core.org_region as f
on a.from_id_org = f.id_region
left outer join core.company as g
on a.from_id_company = g.id_company
where datepart(period_date)>= '01Oct2023'd and datepart(period_date) <= '31Oct2023'd and f.id_country =10987 and c.id_payment_status in (1,3)
and a.id_invoice_type in (1,2);
quit;

I've uploaded a screenshot of the 2 output datasets to show the issue

Re: Sql passthrough not displaying non ascii characters correctly

ballardw — Wed, 08 Nov 2023 23:25:50 GMT

Probably should show the definitions of the libname statements.

One connection is likely defaulting to use of ASCII and the other isn't but why/how may want a few more details.

Re: Sql passthrough not displaying non ascii characters correctly

Patrick — Thu, 09 Nov 2023 03:37:10 GMT

There is likely something missing in your connect statement that instructs SAS how to convert such characters.

If you've got already a working libname then why not use this libref instead in your connect statement?

Change below from:

connect to mysql as myconn (user=&user. password=&password. server=&server. port=3306 database=core);

to:

connect using <working libref> as myconn;

From the docu for the connect statement

Re: Sql passthrough not displaying non ascii characters correctly

ScoobieR — Thu, 09 Nov 2023 08:35:06 GMT

Awesome- thanks Patrick - this works. I didn't know "connect using" was an option. I really appreciate you taking the time to reply.

Re: Sql passthrough not displaying non ascii characters correctly

ScoobieR — Thu, 09 Nov 2023 08:37:23 GMT

hi there - thank you for replying. Just checked and libnames are using non ascii characters. Have got a solution now from another reply.
Appreciate your time

topic Re: Sql passthrough not displaying non ascii characters correctly in SAS Programming

Sql passthrough not displaying non ascii characters correctly

Re: Sql passthrough not displaying non ascii characters correctly

Re: Sql passthrough not displaying non ascii characters correctly

Re: Sql passthrough not displaying non ascii characters correctly

Re: Sql passthrough not displaying non ascii characters correctly