Change the length of a character variable: proc sql modify

[Sir_Highbury]

Dear experts,

on the basis of our explanation I am trying co change my data as it follows:

1. increase the length of the variable:

proc sql;
alter table DC.input_analysis_res
modify Default_value char(20) format=$20. informat=$20.;
quit;

2. insert the string "No_def_v"
/* set unique value in case of default value missing */
data DC.input_analysis_res; set DC.input_analysis_res; if Default_value in ('',' ','-','.') then Default_value='No_def_v';run;

What is wrong with it? I got in the new varibale only the first two digits, i.e. "No" without the rest "_def_v"

[BrunoMueller · Accepted solution]

Since your second step (DATA Step) recreates the original data, you could simply use a LENGTH statement before the SET statement to increase the length of a variable. To me your code looks correct.

Try the code below for a sample program using SASHELP.CLASS

proc sql;
  create table newclass as 
    select * from sashelp.class
  ;
  alter table newClass
    modify name char(20)
  ;
quit;

data want;
  set newclass;

  if sex = "M" then do;
    name = catx(":", "CHG", name, sex, age);
  end;
run;

data want2;
  length name $ 20;
  set sashelp.class;

  if sex = "M" then do;
    name = catx(":", "CHG", name, sex, age);
  end;
run;

Bruno

Editor's note:
As suggested by @Ksharp, a character variable format can cause only a few of the available characters to display, even if the length has been changed successfully. Always check the metadata for actual length after modifying a table. For example:

data work.cars_data_step_length
	  work.cars_sql_modify;
   /* Use LENGTH before SET to change length */
   length Make $200;
	set sashelp.cars (obs=5);
run;

proc sql;
/* Use ALTER TABLE / MODIFY to change length */
alter table work.cars_sql_modify
  modify Make char(100)
;
title "Final Variable Lengths ";
select catx('.',libname,Memname) as Table, Name, Type, Length 
   from dictionary.columns 
   where memname like 'CARS%'
	   and libname in ('SASHELP','WORK')
	   and Name='Make';
quit;

For information about removing unwanted formats, resizing, and renaming table columns, see the SAS Tutorial "Resize, Rename and Reformat Data with SAS Macros".

[Reeza]

RTM - this note is shaded in grey. 

You can't change the length of a variable using the modify statement. You'll have to use a length statement before the Set statement as identified already. 

[Ksharp]

It is really weird . Maybe your format is still $2. .Change it in data step and see what happened.
The following code worked for me .



data have;
 set sashelp.class;
run;

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

data have;
 set have;
 if sex='F' then sex='XXXXXXX';
 format sex $20.;
run;

proc print;run;





OUTPUT:

Obs	Name	Sex	Age	Height	Weight
1	Alfred	M	14	69.0	112.5
2	Alice	XXXXXXX	13	56.5	84.0
3	Barbara	XXXXXXX	13	65.3	98.0
4	Carol	XXXXXXX	14	62.8	102.5
5	Henry	M	14	63.5	102.5
6	James	M	12	57.3	83.0
7	Jane	XXXXXXX	12	59.8	84.5
8	Janet	XXXXXXX	15	62.5	112.5
9	Jeffrey	M	13	62.5	84.0
10	John	M	12	59.0	99.5
11	Joyce	XXXXXXX	11	51.3	50.5
12	Judy	XXXXXXX	14	64.3	90.0
13	Louise	XXXXXXX	12	56.3	77.0
14	Mary	XXXXXXX	15	66.5	112.0
15	Philip	M	16	72.0	150.0
16	Robert	M	12	64.8	128.0
17	Ronald	M	15	67.0	133.0
18	Thomas	M	11	57.5	85.0
19	William	M	15	66.5	112.0