Data - Append

[STEDEMI]

Hi All,

when I use Data- Append table in SAS EG 7.15, I notice that SAS takes into account the string length of the first table and not the maximum length found in other tables with same column types. See attached.

As you can see, the lenght of the field varies depending on which table I open first and which one I append 

I know this can probably be solved with proc sql but I am still learning SQL so no use there

Thoughts?

Stefano

[Kurt_Bremser]

First of all, I see nothing SAS-related in your zip, just a Word document and an Excel file, both of which are useless in a SAS context.

Post code and logs according to https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce, and post SAS data by using https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat....

When using proc append, the base= dataset is kept as is, and new data is appended to that, using the metadata of the base file.

When using a data step, SAS builds the PDV in the order that it encounters variables, so the variable attributes of the first dataset where a variable appears are taken.

Why do you have inconsistent lengths anyway? A proper data model will prevent that.

I guess your problems come from using proc import on Excel files (now I have to clean vomit from my keyboard).

Save to a file format that makes sense (csv), and import that with a user-written data step, and your problems will automagically vanish.

[Patrick · Accepted solution]

@STEDEMI

Your observation is correct. That's how it's documented and how SAS works.

I do agree with everything @Kurt_Bremser writes and for production worthy code you want to have full control over your variable attributes and such length mismatches shouldn't happen in first place.

Now for your adhoc type stuff: Your guess that you might get around the issue via SQL is correct. It's a bit less known but when using a SQL OUTER UNION CORR to combine source tables into a NEW target table (that's NOT appending a source table to another already existing table) then the SQL actually analyses all the variables first and the length of the resulting variable for the combined data will have the max length from all the source tables.

Consider below code sample

data one;
  length var $3;
  var='ABC';
run;
data two;
  length var $5;
  var='ABCDE';
run;
proc sql;
  create table want as
  select * from one
  outer union corr
  select * from two
  ;
quit;

proc contents data=want;
run;