DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

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

[ Edited ]

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

Accepted Solutions
Solution
‎03-09-2016 10:15 AM
Frequent Contributor
Posts: 77

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

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


All Replies
Super User
Posts: 3,254

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

What is the MySQL column type and the encoding method?

Frequent Contributor
Posts: 77

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

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

 

 

Super User
Posts: 3,254

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

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

Super User
Posts: 19,815

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

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.  

Frequent Contributor
Posts: 77

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

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.

 

 

Frequent Contributor
Posts: 77

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

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.

Super User
Posts: 19,815

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

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?

Frequent Contributor
Posts: 77

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

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

Super User
Posts: 19,815

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

Pass through? Post your code please.
Frequent Contributor
Posts: 77

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

 

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;

 

 

Super User
Posts: 11,343

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


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.

Solution
‎03-09-2016 10:15 AM
Frequent Contributor
Posts: 77

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

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 425 views
  • 2 likes
  • 4 in conversation