topic Re: lenght of variables while combining datasets in SAS Programming

lenght of variables while combining datasets

John1231 — Sun, 30 Jul 2017 15:07:58 GMT

hi,

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

dataset_201701

dataset_201702

dataset_201703

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.

Example:

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

data combine;

set dataset_201701 dataset_201702;

run;

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.

Re: lenght of variables while combining datasets

Reeza — Sun, 30 Jul 2017 15:50:33 GMT

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;

Re: lenght of variables while combining datasets

Shmuel — Sun, 30 Jul 2017 19:39:28 GMT

You can use - proc sql; select distionary.columns ... - or - sashelp.vcolomn

in order to create a LENGTH statement with max length of char variables.

For example:

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;

Re: lenght of variables while combining datasets

John1231 — Sun, 30 Jul 2017 18:59:22 GMT

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.

example

dataset 01

variable abc is 30

dataset 02

variable abc is 40

and after running my code the result that i want is:

dataset 01

variable abc is 40

dataset 02

variable abc is 40

Re: lenght of variables while combining datasets

Shmuel — Sun, 30 Jul 2017 19:38:04 GMT

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>;

&len;

set library1.dataset_<any month>;

RUN;

Re: lenght of variables while combining datasets

Reeza — Sun, 30 Jul 2017 21:04:35 GMT

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.

Re: lenght of variables while combining datasets

Patrick — Sun, 30 Jul 2017 22:05:36 GMT

@John1231

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;