Desktop productivity for business analysts and programmers

Data - Append

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Data - Append

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

Attachment

Accepted Solutions
Solution
‎03-14-2018 09:10 AM
Respected Advisor
Posts: 4,566

Re: Data - Append

@STEDEMI

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

 

I do agree with everything @KurtBremser 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;

Capture.JPG

 

View solution in original post


All Replies
Super User
Posts: 9,611

Re: Data - Append

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎03-14-2018 09:10 AM
Respected Advisor
Posts: 4,566

Re: Data - Append

@STEDEMI

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

 

I do agree with everything @KurtBremser 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;

Capture.JPG

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 142 views
  • 1 like
  • 3 in conversation