I want to find out the maximum length of each column of a multiple table using SAS-SQL

[jitendra_patil]

I want to find out the maximum length of each column of a multiple table within the single query using SQL or SAS SQL. Please le me know how we can achieve it in SAS SQL or SQL.

[PaigeMiller]

I don't know what you mean by "maximum length", but the length of each variable in all of your SAS data sets can be found via

proc sql;
    create table column_length as select * from dictionary.columns where libname="WORK";
quit;

In this example, it produces a data set and the length of each variable is the column named LENGTH. If you want to get this information for a different libname, an obvious change to the above code will work.

[jitendra_patil]

Hi PaigeMiller, Really thanks for your prompt response. 'Maximum length' means the column has multiple records and i want to know the max length size of that particular columns record.

I tried your code but its giving me below output. Can you please have a look at it and help me to understand what's wrong with it?

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
75
76 proc sql;
77 create table column_length as select * from dictionary.columns where libname="hadoop";
NOTE: Table WORK.COLUMN_LENGTH created, with 0 rows and 18 columns.
78 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 5222.84k
OS Memory 37288.00k

[PaigeMiller]

Your libname should be in capital letters

HADOOP

There's no easy way to get what you are calling maximum length for many columns across many variables across many data sets, except by writing a macro, and that can't be done in SQL.

[Tom]

Does DICTIONARY.COLUMNS even work for a libref using HADOOP engine?

Do you want to compare the DEFINED length of the variables?  Or the maximum length of the actual values stored in the variable?

To just check the defined length you can use the metadata.  You should also check if the variables are defined with consistent types (numeric or character).

proc sql ;
create table length_report as
select upcase(name) as NAME
       , count(*) as n_datasets
       , max(length) as max_length
       , min(length) as min_length
       , count(distinct length) as n_lengths
       , min(type) as min_type
       , max(type) as max_type
       , count(distinct type) as n_types
from dictionary.columns
where libref='HADOOP'
group by 1
order by 1
;

To actually find the maximum observed length you will need to generate queries against each of the datasets.