Changing the length of a character field in a table to append

[dxtran]

Hello, I'm trying to execute a proc append in SAS EG and got this error

23 proc append base=TELLER_TRAN_01B data=ATM_BRANCH_TRAN_02;
24 run;

NOTE: Appending WORK.ATM_BRANCH_TRAN_02 to WORK.TELLER_TRAN_01B.
WARNING: Variable BRANCHID has different lengths on BASE and DATA files (BASE 7 DATA 6).
NOTE: There were 988 observations read from the data set WORK.ATM_BRANCH_TRAN_02.

Both tables have the following fields:  BZ, BRANCHID, TRANSACTIONS, PERIOD, GROUP.

Visually, BRANCHID appears to have 6 characters (that is what it should have), so I need to change BRANCHID in Base to 6.  The code that creates Base is below.  Can I just set the format of the concatenation to 6 characters? ... Thank you.

proc sql;
CREATE TABLE TELLER_TRAN_01B AS
SELECT distinct
a.BZ,
(a.Bk_Num||a.Branch) AS BRANCHID,
a.TRAN as TRANSACTIONS,
('201912') as PERIOD,
('Tel') as tran_grp
FROM TELLER_TRAN_01 a
GROUP BY a.bzsite_key, (a.Bk_Num||a.Branch), a.TRAN;
quit;

[Shmuel]

You got a warning message, not an error.

As long the BASE variables are long enough to hold the value, you can ignore the warning message. If the appended variable is shorter than base variable you need not to change your code.

You should be aware in case some appended values are longer then the base variable length. In such case you can create a new base with next code:

proc datasets lib=<library> nolist;
        modify <base>;
        length <variable> <new_length>;
quit;

[Tom]

Actually you cannot use PROC DATASETS to change the length of a variable.  It doesn't give an error, but it also does not change the length.

61    proc datasets nolist lib=work;
62    modify class ;
NOTE: The LENGTH attribute cannot be changed and is therefore being ignored.
63     length name $30 ;
64    run;

NOTE: MODIFY was successful for WORK.CLASS.DATA.
65    quit;

[Ksharp · Accepted solution]

data have;
 set sashelp.class;
run;

proc sql;
alter table have
modify sex char(200);
quit;

Or try proc sql instead of proc append:

proc sql;

create table want as

select * from TELLER_TRAN_01B

union all

select * from ATM_BRANCH_TRAN_02

;

quit;

[dxtran]

Thank you!

[Tom]

In your simple example the length will be calculated from the length of the constant in your expression.   In more complicated cases you can explicitly define the length in SQL code by using the LENGTH= option.  For example if you wanted to define PERIOD as length 10 instead of 6 you could code it this way.

proc sql;
CREATE TABLE TELLER_TRAN_01B AS
  SELECT distinct
     a.BZ
    ,(a.Bk_Num||a.Branch) AS BRANCHID
    ,(a.TRAN) as TRANSACTIONS
    ,('201912') as PERIOD length=10
    ,('Tel') as tran_grp 
  FROM TELLER_TRAN_01 a
  GROUP BY a.bzsite_key, (a.Bk_Num||a.Branch), a.TRAN
;
quit;

The LENGTH of a variable cannot be changed once it is created.  The FORMAT used to display the values can be changed.  Be careful about attaching $ format to character variables.  It can confuse you about how the variable defined because of how it is displayed.  If the format width is less than the defined length then you will not see the full values when you print or look at the dataset.  Unfortunately PROC SQL syntax does not have a way to remove formats from a variable.  It is easy to do in a data step (or in PROC DATASETS) using the FORMAT statement by just listing the variable names and not adding any format.

For example to remove the formats attached to all character variables in the dataset BASE you could use:

proc datasets lib=work nolist;
  modify  base;
    format _character_;
  run;
quit;