Using Base SAS to query an ODBC - need help with user generated text structure

[GregG]

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.

[SASKiwi]

What is the MySQL column type and the encoding method?

[GregG]

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

[SASKiwi]

I'm wondering if you have a problem like this: http://support.sas.com/kb/55/704.html

[Reeza]

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]

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]

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]

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]

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]

Pass through? Post your code please.

[GregG]

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]

---

@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 · Accepted solution]

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.