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
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
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.
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
Awesome- thanks Patrick - this works. I didn't know "connect using" was an option. I really appreciate you taking the time to reply.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.