07-30-2017 11:07 AM
I have a library let's say 'library1'.
In this library i have datasets with the same variables (names, type, etc.) Every dataset is created at the end of the month, so i have for example
and so on.
Now, datasets have the same variables, but they could have have different lenghts of observations which can cause unexpected results while combining them.
dataset_201701 has an observation in variable 'abc' which has lenght of 50 .
dataset_201702 has an observation in variable 'abc' which has lenght of 40.
if someone combines these datasets
set dataset_201701 dataset_201702;
It will truncate the variable abc to 40.
So the question is: How can I check the longest observation in every variable, in every dataset in my library 'library1' and set the lenght for the longest observation ?
Basing on my example I want the variable abc to have lenght of 50 in every dataset.
07-30-2017 11:50 AM
Get the lengths from SASHELP.VCOLUMN and isolate the maximum values. Then create a LENGTH statement from those values,
Proc sql; Create table length as Select name, max(length) as max_length From SASHELP.volumns where libname='LIBRARY1' Group name; quit;
07-30-2017 12:08 PM - edited 07-30-2017 03:39 PM
You can use - proc sql; select distionary.columns ... - or - sashelp.vcolomn
in order to create a LENGTH statement with max length of char variables.
proc sort data= sashelp.vcolumn (where=(libname='LIBRARY1' and substr(memname(1,7) = 'DATASET' and type='char' )) out=char_vars; by name descending length; run; data _null_; set char_vars end=eof; by name; length len_stmt $100; /* addapt to max length needed */ retain len_stmt 'length '; if eof then call symput('len', trim(len_stmt)); if first.name; len_stmt = catx(' ',len_stmt, strip(varnmae) , strip(length)); run; data want; &len.; /* line was editted to eliminate double LENGTH keyword */ set dataset_201701 dataset_201702 ... ; run;
07-30-2017 02:59 PM
hey, thank you for your answers but it in not quite what i'm looking for...
the main task is to
1 check the longest observation in every data set for each variable
2 change the length for each variable in whole library to the length of the longest variable.
and i want to do this each time a new dataset is created (every month), not during combination of sets.
variable abc is 30
variable abc is 40
and after running my code the result that i want is:
variable abc is 40
variable abc is 40
07-30-2017 03:38 PM
To your suggestion - " check the longest observation in every data" - I have some remarks:
- you possibly use OPTION COMPRESS=YES (check by running: proc options) - then observation lebgth is not symptomatic.
- if there are more severeal char variables in a dataset, you cannot conclude which variable to expand.
sashelp.vcolumn - holds information on all members (datasets) in all libraries, so there is no need to check each dataset.
Taking max(length) as suggested by @Reeza or taking the first length when sorted by descending - you shall get the same result.
The code I posted relates to your selected library (library1), checks all datasets named with prefix DATASET (names you gave).
The variable NAME is the variable name in the dataset.
I create a macro variable containing the LENGTH statement to use with maximun length of all character variables in those
selected datasets. Finally I show hot to use this macro variable.
You can use it any time you need to concatenate datasets OR you can use it to convert your datasets to be all with same
definitions of maximum length. Using OPTION COMPRESS=YES will compress data so that you save disk space inspite
expanding length of variables.
In order to convert your dataset to standard max length run:
data library1.dataset_<any month>;
set library1.dataset_<any month>;
07-30-2017 05:04 PM
Then what you should actually do, is define your process better with fixed lengths for your values. You should know what the maximums will be and create a table as that structure. Then insert the records into that table.
The solutions provided, scan all tables and take the longest length assigned. This is in line with how your question is posed. Actually scanning each record every time is inefficient. It's better to understand your data and make the limits based on your knowledge of the process rather than building it based on what you see in the data currently.
07-30-2017 06:05 PM
As @Reeza already wrote: You should know your data and define the variable attributes upfront. Analyzing and changing the data every single time sounds like a bad idea.
If your problem is that you're creating monthly data with different variable lengths then combining the data using a PROC SQL UNION will pre-analyse your variable definitions and will take the longest length (=no truncation risk).
proc sql; create table all_months as select * from dataset_201701 outer union corr select * from dataset_201702 ; quit;