Purpose: To display in the output the max length of the values for all the varibles in the dataset.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

Purpose: To display in the output the max length of the values for all the varibles in the dataset.

Hi,

I would like to output the maximum length of the values for all the variables in the dataset.We have requirement that we should assign only +1 length to the maximum record value of variable. if I have a macro which would output the max record length for all the variable. It would facilitate in assigning the lengths for the varaibles.

Thanks

Rocky


Accepted Solutions
Solution
‎02-10-2014 02:31 PM
Super User
Super User
Posts: 6,500

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

Here is an example that will work as long as there are not too many variables in your table.

Only makes sense to do this for character variables.

This will do it for sashelp.class, but you can change the libname and membername to others if you want.

%let libname=sashelp;

%let memname=class;

proc sql noprint ;

select catx(' ','max(length(',name,')) as',name)

   into :codegen separated by ','

   from dictionary.columns

   where libname="%upcase(&libname)"

     and memname="%upcase(&memname)"

     and type = 'char'

;

quit;

proc sql ;

  select &codegen from &libname..&memname ;

quit;

View solution in original post


All Replies
Super User
Posts: 5,257

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

You could use SQL:

select max(length(var1)) as max_var1,

etc

You probably want to wrap the logic into a macro which automatically generates the select statement based on all char variables in the named data set.

Data never sleeps
Solution
‎02-10-2014 02:31 PM
Super User
Super User
Posts: 6,500

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

Here is an example that will work as long as there are not too many variables in your table.

Only makes sense to do this for character variables.

This will do it for sashelp.class, but you can change the libname and membername to others if you want.

%let libname=sashelp;

%let memname=class;

proc sql noprint ;

select catx(' ','max(length(',name,')) as',name)

   into :codegen separated by ','

   from dictionary.columns

   where libname="%upcase(&libname)"

     and memname="%upcase(&memname)"

     and type = 'char'

;

quit;

proc sql ;

  select &codegen from &libname..&memname ;

quit;

Frequent Contributor
Posts: 145

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

Thanks Tom. It worked perfect for me.

Based on those  length values, Is there way we can automate and reassign the length values to all the variable in the dataset of (recorded value+1)

Super User
Super User
Posts: 6,500

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

To change the lengths you really need to recreate the dataset.

One way to do that is to use a DATA step with a LENGTH statement before the SET to define the lengths required.

%let libname=sashelp;

%let memname=class;


proc sql noprint ;

select catx(' ','max(length(',name,')) as',name)

   into :codegen separated by ','

   from dictionary.columns

   where libname="%upcase(&libname)"

     and memname="%upcase(&memname)"

     and type = 'char'

;

create table lengths as

  select &codegen from &libname..&memname

;

quit;


proc transpose data=lengths out=lengthv;

run;

proc sql noprint ;

select varnum

     , catx(' ',name

               ,case when type='char' then cats('$',col1+1)

                     else cats(length)

                end

           )

  into :lengths

     , :lengths separated by ' '

  from lengthv a

       right join

       (select name,varnum,type,length from dictionary.columns

        where libname="%upcase(&libname)"

          and memname="%upcase(&memname)") b

  on upcase(a._name_) = upcase(b.name)

  order by varnum

;

quit;

data new ;

  length &lengths ;

  set &libname..&memname ;

run;

Frequent Contributor
Posts: 145

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

Thanks Tom......it served purpose and I was able to reassign the lengths...........

Occasional Contributor
Posts: 14

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

Tom--

The code *almost* worked for me.  At the last step: 

data new ;

  length &lengths ;

  set &libname..&memname ;

run;

I get warnings on every text variable that "multiple lengths were specified..." and in my dataset NEW, the lengths are the same as before.  I did a %put &lengths to check the value, and it does have the correct lengths that I am expecting.  I thought if you put the LENGTH statement before the SET, it would override input lengths.  Is it because the lengths in my dataset are formats (i.e., "format VAR $50."  instead of "length VAR $50")?

Thanks!

Suzy

Super User
Super User
Posts: 6,500

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

The length is different than the format.  A format is just instructions to SAS for how to display the variable.  Attaching $nn formats to character variables is generally useless and can cause confusion in cases such as yours.  You should not use a FORMAT statement as short hand for defining your variables.  Use a LENGTH or ATTRIB statement.

To remove the formats you can use a format statement that lists variable names but no format.

data new ;

  length &lengths ;

  set &libname..&memname ;

   format _character_ ;

run;

Occasional Contributor
Posts: 14

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

Thanks!  I still get the warnings in my log, but at least now I'm getting the correct lengths!  Smiley Happy

Suzy

Occasional Contributor
Posts: 14

Re: Purpose: To display in the output the max length of the values for all the varibles in the dataset.

One small glitch--the new dataset is missing the dataset label.  Is there a way (short of typing it out in a label statement) to force the label to carry through?

Suzy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 491 views
  • 6 likes
  • 4 in conversation