DATA Step, Macro, Functions and more

change the length of a charachter variable: proc sql modify

Reply
Frequent Contributor
Posts: 127

change the length of a charachter variable: proc sql modify

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"

SAS Super FREQ
Posts: 685

Re: change the length of a charachter variable: proc sql modify

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

Super User
Posts: 17,960

Re: change the length of a charachter variable: proc sql modify

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. 

 

Super User
Posts: 9,691

Re: change the length of a charachter variable: proc sql modify

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

Ask a Question
Discussion stats
  • 3 replies
  • 394 views
  • 0 likes
  • 4 in conversation