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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.