BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GregG
Quartz | Level 8

We have a survey website that stores to a MySQL database, that I use Base SAS (via passthrough) to connect to via ODBC.

 

The user generated text has a "+" (plus sign) for all spaces between words and other "special encoding" - %2c instead of , as another example.

 

What is the best way for me to translate these back to what I'm used to seeing, a space between words and a comma instead of %2c?

 

I've attached a screenshot of the field / text in question.

 

I've also looked at the data in SQL Server Management Studio and it is formatted as expected - , (comma) instead of %2c, etc.

(ETA: I was looking at Top 1000, which for some reason was correct. However, looking at current data, shows "+" for spaces, "%2c" for commas, etc.

Thank you in advance.


sas_encoding.png
1 ACCEPTED SOLUTION

Accepted Solutions
GregG
Quartz | Level 8

Okay, so I was not able to go upstream to fix the process at its source.

 

However, I was able to solve the problem using a function I wasn't aware of - urldecode().

 

Qual_Text = urldecode(qvar);

 

This completely solved the issue I was having.

 

I had seen the exact text I referenced described as "URL encoded" text, but I dismissed that as a possible solution because I wasn't dealing with a URL.

 

Anyway, I'd like to thank everyone for their time and help.

 

View solution in original post

12 REPLIES 12
SASKiwi
PROC Star

What is the MySQL column type and the encoding method?

GregG
Quartz | Level 8

Data Type of column = varchar

Length = 1000

I don't see the encoding listed as "encoding", but I do see "Collation" as SQL_Latin1_General_CP1_CI_AS

 

 

Reeza
Super User

The best way is to fix the way it's imported/read in, it sounds like an encoding issue. Was the MySQL database on Unix and now you're on Windows or vice versa?

 

If you access the DB in Excel does it show the same issue?

 

If you wanted to fix it in SAS, you could use the TRANSLATE or TRANSWRD function to replace characters or words.  

GregG
Quartz | Level 8

The MySQL database is probably on a Linux server, and yes, I am accessing it from Windows. I haven't attempted to access it from Excel.

 

 

GregG
Quartz | Level 8

Would the most efficient way involve using the translate for each different occurence?

 

Is there away to change my encoding in the SAS environment to account for that in a single program?

 

Thank you again.

Reeza
Super User

When connect to a MS SQL server via ODBC there's an option to :

Translate of Character Variables 

 

Is that available in your ODBC menu?

GregG
Quartz | Level 8

I don't think I can answer that question. I can view the databases using SQL Server Management Studio, but I bring them into SAS via passthrough connection.

 

The web survey and MySQL database I neither created nor maintain.

 

So I am not sure what you are asking with regard to ODBC menu.

 

Thank you again

Reeza
Super User
Pass through? Post your code please.
GregG
Quartz | Level 8

 

PROC SQL;
Connect to ODBC AS QEP (DSN=QEP user="&myusername" password="&mypassword" );
Create table qep_stu_query as
	Select * from connection to QEP
		(
		SELECT	C.SEC_TERM AS "SEC_TERM",
			C.COURSE_SECTIONS_ID AS "COURSE_SECTIONS_ID",
			C.SEC_SUBJECT AS "SEC_SUBJECT",
			C.SEC_COURSE_NO AS "SEC_COURSE_NO",
			C.SEC_NO AS "SEC_NO",
			C.SHORTTITLE AS "COURSETITLE",
			TU.ENABLED AS "ENABLED",
			QEP.OPENDATE AS "OPENDATE",
			QEP.CLOSEDDATE AS "CLOSEDDATE",
			A.ID AS "ASSESSMENT_ID",
			TU.COLID AS "S_COLID",
			TU.USERNAME AS "S_USERNAME",
			TU.RESPONSIBILITY AS "RESPONSIBILITY",
			TU.FIRSTNAME AS "S_FIRSTNAME",
			TU.LASTNAME AS "S_LASTNAME",
			R.SUBMITTEDBYUSERNAME AS "SUBMITTEDBYUSERNAME",
			R.QBIT AS "QBIT",
			R.QVAR AS "QVAR",
			R.QINT AS "QINT",
			R.APPLICABLE AS "APPLICABLE",
			R.FINISHED AS "FINISHED",
			R.ABOUTUSERNAME AS "ABOUTUSERNAME",
			Q.QNUMBER AS "QNUMBER",
			Q.SHORTTITLE AS "QTITLE",
			Q.LONGDESCRIPTION AS "QDESCRIPTION",
			Q.LEFTANCHOR AS "LEFTANCHOR",
			Q.RIGHTANCHOR AS "RIGHTANCHOR",
			Q.MIN AS "MIN",
			Q.MAX AS "MAX",
			Q.QTYPE AS "QTYPE",
			A.TITLE AS "TITLE",
			A.DESCRIPTION AS "DESCRIPTION",
			A.GROUPS AS "GROUPS",
			SE.OVERRIDE AS "OVERRIDE"
		FROM	RESPONSES R
			JOIN QUESTIONS Q ON	R.QUESTIONID = Q.ID
			JOIN COURSES C ON R.COURSE_SECTIONS_ID = C.COURSE_SECTIONS_ID
			JOIN QEPCOURSES QEP ON R.COURSE_SECTIONS_ID = QEP.COURSE_SECTIONS_ID
			JOIN ASSESSMENTS A ON A.ID = QEP.ASSESSMENTID
			JOIN TWUUSERS TU ON TU.USERNAME = R.SUBMITTEDBYUSERNAME
			JOIN STUDENTENROLLMENT SE ON (
						SE.USER_NAME = R.ABOUTUSERNAME AND
						SE.COURSE_SECTIONS_ID = R.COURSE_SECTIONS_ID
						)
		WHERE	A.ID = 1
		    AND Q.ASSESSMENTID = 1
		    AND QEP.ENABLED = 1
		    AND SE.OVERRIDE = 0
		    AND C.SEC_TERM = &TERM
		);								
QUIT;

 

 

ballardw
Super User

@GregG wrote:

We have a survey website that stores to a MySQL database, that I use Base SAS (via passthrough) to connect to via ODBC.

 

I've also looked at the data in SQL Server Management Studio and it is formatted as expected - , (comma) instead of %2c, etc.

(ETA: I was looking at Top 1000, which for some reason was correct. However, looking at current data, shows "+" for spaces, "%2c" for commas, etc.

Thank you in advance.


Your last comment about "Top 1000" different from "current" coupled with a website makes me suspect some change done by the survey software collecting or extracting the data. It might be worth going upstream in the data process to head off possible future issues.

GregG
Quartz | Level 8

Okay, so I was not able to go upstream to fix the process at its source.

 

However, I was able to solve the problem using a function I wasn't aware of - urldecode().

 

Qual_Text = urldecode(qvar);

 

This completely solved the issue I was having.

 

I had seen the exact text I referenced described as "URL encoded" text, but I dismissed that as a possible solution because I wasn't dealing with a URL.

 

Anyway, I'd like to thank everyone for their time and help.

 

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
  • 12 replies
  • 1535 views
  • 2 likes
  • 4 in conversation