BookmarkSubscribeRSS Feed
dllfrancelino
Fluorite | Level 6

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

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Tom
Super User Tom
Super User

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ß

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1663 views
  • 1 like
  • 3 in conversation