BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ScoobieR
Obsidian | Level 7

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

 

ScoobieR_1-1699485567349.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

Patrick_0-1699500928372.png

 

View solution in original post

4 REPLIES 4
ballardw
Super User

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.

ScoobieR
Obsidian | Level 7
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
Patrick
Opal | Level 21

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

Patrick_0-1699500928372.png

 

ScoobieR
Obsidian | Level 7

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1206 views
  • 0 likes
  • 3 in conversation