This is a perfect opportunity to leverage some PROC SQL 🙂 Assuming your imported data set is "lib.have" and the name of the character variable you want to modify is "TextVar", this should work for you:
proc sql; alter table lib.have modify TextVar char(20) format=$20. informat=$20.; quit;
This is one of my pet peeves with SAS's implementation of importing data from databases. They always attach both a format and an informat to character variables. As you have seen this can cause a number of headaches when you start combining that data with other datasets.
In general permanently attaching the $ format to a character variable adds nothing of value.
The only way I know to remove these is to use the FORMAT (or INFORMAT) statement with a variable list, but without a format.
data sasfile ;
set dbfile ;
format _character_ ;
run;
You can use PROC DATASETS to modify variable characteristics, including formats. Here is some sample code to play with:
data test; set sashelp.class; format Name $8. weight z5.1; run; title "Formatted values"; proc print; run; /*Get a list of variable names in a macro variable*/ proc sql noprint; select Name into :Varnames separated by ' ' from dictionary.columns where libname='WORK' and memname='TEST'; quit; /*Remove formats from all the variables*/ proc datasets library=work nolist; modify test; format &varnames; quit; title "After formats removed"; proc print; run;
SASJedi.
There is no need to use dictionary table any more , directly use variable list _all_;
data test;
set sashelp.class;
format Name $8. weight z5.1;
run;
title "Formatted values";
proc print; run;
/*Remove formats from all the variables*/
proc datasets library=work nolist;
modify test;
format _all_;
quit;
title "After formats removed";
proc print; run;
Ksharp
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"
Open a new topic if you have a new question.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.