I can't change the length of a character column after Proc Import process

Question

The first proc import process reads a "character" column from an Excel file. The data from the Excel is only 2 characters, so SAS creates the column length as 2.

proc import datafile = "excelfie" out=MainTable DBMS = excel REPLACE; 
            SHEET = "Sheet1"; GETNAMES=NO; MIXED=YES; USEDATE=YES; SCANTIME=YES; RANGE="A1:C26"; 
run;

Then I insert another SAS-table (with same column names) into the main SAS-table by using proc append, but I get an error, because SAS created the column in main table for 2 character length and the new character data is 5 digits.

Proc Append Base=MainTable Data=Table1; Run; 

I tried to change the length of the column before the proc attend process as

data MainTable;
    set MainTable;
        format Column2 $5.;
        informat Column2 $5.;
        length Column2 $5;
run;

Proc Append Base=MainTable Data=Table1; Run; 

But I still get the same error, because the Column format is now $5., but the length is still 2.

I used the Force option in the Proc append process which forces the merge process for data with different formats.

Proc Append Base=MainTable Data=Table1 FORCE NOWARN; Run; 

Now I don't get error, but it cuts off the new data from 5 digits to 2 digits. So what should I do?

Answer 1

In your code the "new" MainTable takes the variables of the "old" one, then reads the LENGTH statement and ignores it, since the variable Column2 has been defined from "old" MainTable. You have to define the new length BEFORE the SET statement.

data MainTable;
    format Column2 $5.;
    informat Column2 $5.;
    length Column2 $5;
    set MainTable;
run;