08-22-2013 01:20 PM
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:
%do i=1 %to &m;
create table length_&i as
select max(length(&&name&i)) as '"&&name&i"'n
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.
08-22-2013 01:49 PM
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:
create table max_test
max(length("&names1"n)) as "&name1"n
08-22-2013 01:58 PM
Do the names include double quotes? In that case you can use QUOTE() function.
08-22-2013 02:21 PM
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.
08-22-2013 02:25 PM
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
Try this experiment with one of the names that is giving you trouble.
options validvarname=any ;
%let name1=a @ b ;
set sashelp.class ;
rename name=%sysfunc(quote(&name1))n ;
proc sql ;
select max(length(%sysfunc(quote(&name1))n)) as %sysfunc(quote(&name1))n
08-22-2013 02:30 PM
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.
08-22-2013 02:50 PM
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.
maybe a hash to store the max length for each variable.
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.