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.
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.
What is the MySQL column type and the encoding method?
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
I'm wondering if you have a problem like this: http://support.sas.com/kb/55/704.html
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.
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.
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.
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?
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
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;
@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.
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.
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.
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.