topic Re: Error in SAS macro due to special characters in a variable in SAS Programming

Error in SAS macro due to special characters in a variable

Haemoglobin17 — Wed, 19 Oct 2022 10:09:13 GMT

Hi everyone!

I am studying the macro 1 course and I am doing the exercise m104p01. Unfortunately, when I run it, the program produces a table of 12 obs, then stops and it give me the two following error messages:

ERROR: Invalid characters were present in the data.

ERROR: An error occurred while processing text data.

I discovered that the observation the caused this error (that would e the 13th) is one in which the variable name contains an invalid character (I suppose an accent or something like this). As a confirm, If I create a database and I change all the values of name with "Iron Man" it runs perfectly. I was wondering if you know some tricks that helps me to force SAS to accept this special characters. Below you can find the code of the exercise. Thank you all 😁

/* Part b. */
%macro customers(type); 
title "&type Customers"; 
proc sql number;
select Name, Age_Group, Type 
    from mc1.customers 
    where Type contains "&type"; 
quit; 
title;
%mend customers;

%customers(Gold)

Re: Error in SAS macro due to special characters in a variable

PaigeMiller — Wed, 19 Oct 2022 11:20:56 GMT

It would help (now, and 100% of the time in the future) for you to show us the ENTIRE log for this macro. Please first turn on macro debugging options by running this command:

options mprint symbolgen;

Then, run the macro again, copy the ENTIRE log as text, and paste it into the window that appears when you click on the </> icon. Do NOT show us partial logs.

Re: Error in SAS macro due to special characters in a variable

Haemoglobin17 — Wed, 19 Oct 2022 12:33:36 GMT

Hi PaigeMiller,

Thank you for the answer and for the advice! This is the log that I receive:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

SYMBOLGEN: Macro variable GRAPHINIT resolves to GOPTIONS RESET=ALL GSFNAME=_GSFNAME;

70 options mprint symbolgen;

SYMBOLGEN: Macro variable TYPE resolves to Gold

72 %customers(Gold)

MPRINT(CUSTOMERS): title "Gold Customers";

MPRINT(CUSTOMERS): proc sql number;

SYMBOLGEN: Macro variable TYPE resolves to Gold

MPRINT(CUSTOMERS): select Name, Age_Group, Type from mc1.customers where Type contains "Gold";

ERROR: Invalid characters were present in the data.

ERROR: An error occurred while processing text data.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

MPRINT(CUSTOMERS): quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.02 seconds

user cpu time 0.02 seconds

system cpu time 0.00 seconds

memory 5737.18k

OS Memory 30632.00k

Timestamp 19/10/2022 12:28:39 PM

Step Count 46 Switch Count 0

Page Faults 0

Page Reclaims 108

Page Swaps 0

Voluntary Context Switches 6

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

MPRINT(CUSTOMERS): title;

74 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

SYMBOLGEN: Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;

Re: Error in SAS macro due to special characters in a variable

PaigeMiller — Wed, 19 Oct 2022 12:53:17 GMT

Please don't ignore the instructions that I gave, about using the </> icon.

Anyway, unfortunately, in this case, the full log isn't any more helpful.

Can you please show us a screen capture of that variable in the 13th record? Use the "Insert Photos" icon to include your screen capture in your reply, do not attach files.

Re: Error in SAS macro due to special characters in a variable

Quentin — Wed, 19 Oct 2022 13:28:50 GMT

Interesting, from a quick google search of the error message, it looks like this is an encoding problems (i.e. the encoding of your session doesn't match the encoding of the data, and SAS isn't able to transcode the values). See e.g. https://communities.sas.com/t5/SAS-Studio/ERROR-Invalid-characters-were-present-in-the-data/td-p/556840.

Just to double check that it's not a macro problem, I would try:

proc sql number;
select Name, Age_Group, Type 
    from mc1.customers 
    where Type contains "Gold"; 
quit; 

data want ;
  set mc1.customers ;
  where Type contains "Gold"; 
run ;

proc sql number;
select Name, Age_Group, Type 
    from mc1.customers ;
quit; 

data want ;
  set mc1.customers ;
run ;

I wonder if it is the WHERE clause that is having the problem. Do the above examples without the WHERE clause/statement work?

Re: Error in SAS macro due to special characters in a variable

Haemoglobin17 — Thu, 20 Oct 2022 08:16:16 GMT

Hi PaigeMiller,

Thank you, I have uploaded a screenshot of part of the database from Sashelp I have been working with. The program stops while evaluating the row 31, that, according to the where statement, should be put in the output dataset. If I change the value of the name it doesn't happens.

Re: Error in SAS macro due to special characters in a variable

Haemoglobin17 — Thu, 20 Oct 2022 08:19:02 GMT

Hi Quentin!

Thank you very much for the replay! I run the program and the error occurs both if the script with the where statement and that without. I thinks that the cause could be the presence of non recognized symbols in the variable name, because when I change these values the problem disappears!

Re: Error in SAS macro due to special characters in a variable

Quentin — Thu, 20 Oct 2022 13:23:26 GMT

Can you submit:

%put %sysfunc(getoption(encoding)) ;

and post what you get back in the log?

I think you're right, the problem is likely the special characters in some of the values. If you're SAS session is using the wrong encoding, it may not be able to interpret them. When you start the SAS session, you can choose an encoding.

How are you running SAS? Is this local SAS on your PC, or SAS on a server, or Viya? Is it SAS On Demand for Academics? Depending on how you are running it, you may have varying levels of control over the encoding.

Re: Error in SAS macro due to special characters in a variable

Tom — Thu, 20 Oct 2022 13:32:15 GMT

Either fix the dataset encoding, the SAS session encoding, or tell SAS to ignore the encoding.

select Name, Age_Group, Type 
    from mc1.customers (encoding='any')
    where Type contains "&type"
;

Re: Error in SAS macro due to special characters in a variable

Kurt_Bremser — Fri, 21 Oct 2022 11:03:45 GMT

If your SAS session runs with UTF encoding, but the data contains single-byte WLATIN9 "special characters", those can be invalid, as they are only allowed as UTF continuation bytes.

Re: Error in SAS macro due to special characters in a variable

Haemoglobin17 — Fri, 21 Oct 2022 12:44:17 GMT

Thank you very much Tom!