BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sir_Highbury
Quartz | Level 8

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"

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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".

 

View solution in original post

3 REPLIES 3
BrunoMueller
SAS Super FREQ

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
Super User

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
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 31915 views
  • 0 likes
  • 4 in conversation