Name literals in sql with macros

Reply
Occasional Contributor
Posts: 18

Name literals in sql with macros

Hi all,

I'd like to write a loop to go through all the variables in a dataset and create a new datasets with the maximum length of the variables.

Something along these liens:

M is a macro variable representing the number of variables

names is the macro variable which represents the variables, so names1 is the first variable, names2 the second, and so on.

My code is as follows:

%macro lengthloop

%do i=1 %to &m;

proc sql;

create table length_&i as

select max(length(&&name&i)) as '"&&name&i"'n

from dataset;

quit;

%end;

%mend lengthloop;

%lengthloop;

You'll notice that the second invocation of name&i, the one in triple quotes, is in the name literal format -- that is becomes some of the variable names have non letter, non numeric characters.

However, the name literal will not resolve in the sql macro -- instead of resolving to the name of the variable, it resolves to the name of the variable preceeded by a quotation mark.

Any ideas?

Thanks!

Super User
Super User
Posts: 7,035

Re: Name literals in sql with macros

You have too many quotes.  If you need to use a name literal then you need it in both places.


select max(length("&&name&i"n)) as "&&name&i"n

Occasional Contributor
Posts: 18

Re: Name literals in sql with macros

Tom,

Thanks for the quick reply.  The double quotes worked in the instances in which the variable did not have a special character.  However, when referencing a variable with a special character in its name, the sql query generates the following error:

ERROR: Function LENGTH requires a character expression as argument 1.

Code looks as follows:

proc sql;

create table max_test

as select

max(length("&names1"n)) as "&name1"n

from 'test'n;

quit;

Thanks

Super User
Super User
Posts: 7,035

Re: Name literals in sql with macros

Do the names include double quotes?  In that case you can use QUOTE() function.

select max(length(%sysfunc(quote(&&name&i))n))

    as %sysfunc(quote(&&name&i))n

Super User
Super User
Posts: 7,035

Re: Name literals in sql with macros

Are you sure that message is not caused by the use of &nameS1 instead of &name1 ?

Occasional Contributor
Posts: 18

Re: Name literals in sql with macros

Certain of it -- the typo you caught (name/names) came from me retyping the code for the forum.  In my actual program, both instances use the macro variable  'names'.  Good eye!

The issue that some of the names contain special characters like '$', '#,' or '@.

The sysfunc/quote command results in the same error as before when attempting to resolve a variable that ends in a '$'

That is, ERROR: Function LENGTH requires a character expression as argument 1.

Super User
Super User
Posts: 7,035

Re: Name literals in sql with macros

Are you sure your source data are in SAS datasets?  Perhaps you are hitting some limit on SAS/Access?

Are you sure your variable is a character variable?  If the variable is a numeric variable then you could find the maximum display length with something like

max(length(strip(put(%sysfunc(quote(&name1))n,best32.))))

Try this experiment with one of the names that is giving you trouble.

options validvarname=any ;

%let name1=a @ b ;

data x;

set sashelp.class ;

rename name=%sysfunc(quote(&name1))n ;

run;

proc sql ;

select max(length(%sysfunc(quote(&name1))n)) as %sysfunc(quote(&name1))n

from x

;

quit;

Occasional Contributor
Posts: 18

Re: Name literals in sql with macros

Tom,

You're exactly right -- the variable is numeric.  Of course it has no length!

Good catch.  Now to find a way to ignore those variables in the rest of my code.

Thanks a bunch--the sysfunc/quotes commands, around the non-numeric ones, seem to have fixed the bulk of the issues I was dealing with.

Respected Advisor
Posts: 3,799

Re: Name literals in sql with macros

It's good to learn how to code with name literals.  You might be interested in the NLITERAL function too.

However if you are going to measure and trim the character variables to optimal length you can do all the measurements is a single data step.

use

set ...;

array _c

  • _character_;
  • maybe a hash to store the max length for each variable.

    measure

    at the end use output method to output the hash data of max length.

    Then a little code gen to recreate the data set with a new length for each character variable.

    I think if we toss in that name literal function here or there this would work with validvarname=ANY.

    https://communities.sas.com/message/163032#163032

    Ask a Question
    Discussion stats
    • 8 replies
    • 1154 views
    • 0 likes
    • 3 in conversation