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"
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".
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".
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.