CODEWITHSUNDEEP
sqlsas
sampson7185
sampson7185

Reputation: 23

SAS: SELECT...INTO causes a random single quote to be added to end

When I do a SELECT...INTO statement for a particular WHERE it causes there to be a random single quote at the end. I am using a proc sql through SAS to run the following

proc sql noprint;
    SELECT DISTINCT PrimaryAgent, PrimaryAgentName
       INTO :AgentID separated by '|', :AgentName separated by '|'
    FROM test.OUTPUT_REPORTDATATAB
    WHERE Region = "Western" AND District = "Saskatchewan";
quit;

AgentID will have all the values separated by | as usual but the AgentName will have a single quote on the end (e.g. test|test2|test3') when I go to do a %put.

This issue only happens for this combination of Region and District, any other combination is fine.

I did notice that this Region and District combo is sitting at the bottom of the dataset but I would imagine it would cause the same issue for both AgentID and AgentName if that was the root of the issue.

Any ideas on what might be causing this? Or any way I could remove this single quote?

I have tried substr and translate but as soon as I try to pass in my &AgentName. It breaks the function (says I'm missing a parentheses).

I have even tried adding an escaped and non-escaped single quote to the front so it at least has matching quotes but that doesn't seem to work either.

Upvotes: 0

Views: 453

Answers (3)

Joe
Joe

Reputation: 63434

Assuming the single quote is actually in the data - I think highly likely, particularly as you say it is the last row in the dataset; likely there is a quote in your input data - you can use compress to remove it.

proc sql;
    SELECT DISTINCT compress(PrimaryAgent,"'") , compress(PrimaryAgentName,"'")
    INTO :AgentID separated by '|', :AgentName separated by '|'
        FROM test.OUTPUT_REPORTDATATAB
    WHERE Region = "Western" AND District = "Saskatchewan";
quit;

Bingo, no single quote (Assuming single quotes aren't normally allowed in those fields). If they are sometimes allowed in those fields, it's a stickier problem that requires more information from you to answer.

Upvotes: 0

Tom
Tom

Reputation: 51621

First make sure you aren't trying to put too many characters into a macro variable. Macro variables can only be 65K characters long.

SAS is NOT going to insert any characters that are not in your data. So if you see test3' as part of the value of your macro variable then that most be part of the value of the dataset variable you used to populate it with.

Check variables AgentID and AgentName for quotes.

If you don't find any then check for other non-printing characters that might be confused by the macro processor as quotes or look like quotes to you when printed to the LOG.

Upvotes: 1

Nathan A
Nathan A

Reputation: 29

Hmm, very strange. Your code looks fine to me. And I created a similar (assuming here) dataset, ran your code on it, and I'm not getting any single quotes.

%put &agentName.;

for me, returns - exactly how I would expect.

Name 1|Name 3|Name 6

Can you post your ingoing dataset - or at least a few relevant records? Alternatively, here is the code I wrote - look and see if something is different from what you've got. But the useful chunk here is ripped 100% from your post (with a noprint added for testing).

data output_reportdatatab;
primaryAgent=1; primaryAgentName="Name 1"; region="Western"; District="Saskatchewan"; output;
primaryAgent=2; primaryAgentName="Name 2"; region="Eastern"; District="Saskatchewan"; output;
primaryAgent=3; primaryAgentName="Name 3"; region="Western"; District="Saskatchewan"; output;
primaryAgent=3; primaryAgentName="Name 3"; region="Eastern"; District="Saskatchewan"; output;
primaryAgent=3; primaryAgentName="Name 3"; region="Western"; District="Saskatchewan"; output;
primaryAgent=3; primaryAgentName="Name 3"; region="Western"; District="Saskatchewan"; output;
primaryAgent=4; primaryAgentName="Name 4"; region="Western"; District="Alberta"; output;
primaryAgent=4; primaryAgentName="Name 4"; region="Western"; District="Manitoba"; output;
primaryAgent=5; primaryAgentName="Name 5"; region="Eastern"; District="British Columbia"; output;
primaryAgent=6; primaryAgentName="Name 6"; region="Western"; District="Saskatchewan"; output;
run;

proc sql noprint;
    SELECT DISTINCT PrimaryAgent, PrimaryAgentName
    INTO :AgentID separated by '|', :AgentName separated by '|'
        FROM OUTPUT_REPORTDATATAB
    WHERE Region = "Western" AND District = "Saskatchewan";
quit;

%put &agentName.;

Upvotes: 0

Related Questions