How to use BASECHAR and UPCASE in a Macro Variable %LET?

[dllfrancelino]

Dear all,

I'm trying to create this simple list you can find below.

I want the PROC SQL to correct the lower case and also to correct if the variable is with special caracteres ç or í ê î ô  etc...

Right now if i'm typing tp_subsidio the code is running fine (so the UPCASE is working)

But if i write tp_subsÍdio the BASECHAR is not working, as you can also see from the log below.

How do I solve this?

%LET VARIABLE = tp_subsÍdio;

PROC SQL;
SELECT DISTINCT BASECHAR(UPCASE(&VARIABLE)) AS LIST_&VARIABLE
FROM BS_PORT.BS_PORT_&EX.;
QUIT;

*** log ***

25         %LET VARIABLE = tp_subsídio;
26         


27         PROC SQL;
28         SELECT DISTINCT BASECHAR(UPCASE(&VARIABLE)) AS LIST_&VARIABLE
SYMBOLGEN:  Macro variable VARIABLE resolves to tp_subsídio
SYMBOLGEN:  Macro variable VARIABLE resolves to tp_subsídio
29         FROM BS_PORT.BS_PORT_&EX.;
SYMBOLGEN:  Macro variable EX resolves to 201806
ERROR: The following columns were not found in the contributing tables: 'tp_subsídio'n.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
30         QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[PaigeMiller]

First of all, you need to know that macros really just perform text substitution, the value of the macro variable is used in place of the macro variable when the code is run. And furthermore, when this text substitution happens when you run the code, LEGAL VALID SAS code must be created.

So, when you run your code, this line is created (text substition is shown in red)

SELECT DISTINCT BASECHAR(UPCASE(tp_subsídio)) AS LIST_tp_subsídio

which is not valid SAS code because, as explained in the error message,

The following columns were not found in the contributing tables: 'tp_subsídio'n

There is no such variable, so UPCASE can't work on a variable that doesn't exist. You need to provide a variable name that actually exists in the data set.

[Tom]

Answering the question in your subject line and ignoring the gibberish in your question content.

Here is an example using one of the values in the examples in the documentation.

%let original=Mühlenfließ ;
%let changed=%sysfunc(basechar(%upcase(&original)));
%put &=original &=changed ;

ORIGINAL=Mühlenfließ CHANGED=MUHLENFLIEß