Hi,
I have a dataset with 43 variables. For example, var1-var43. In that about 28 are character and the remaining numeric. I want to identify those numeric variables and convert them to character. My need is I want an efficient SAS code in the use of put function or equivalent.
Does it have to involve writing put function and storing in a new variable for each and every numeric variable?/* a tedius process or long lines*/ or is there anybody who can help me with a resuable super macro?
Thanks,
Charlotte from England
Yes, the SASHELP library contains metadata about the datasets and libraries SAS knows about. By pulling this over into my dataset I am getting that metadata to work with. So say I have a dataset HAVE in the WORK library and it has variable var1-3, the metadata on the table looks something like:
LIBNAME MEMNAME NAME TYPE...
WORK HAVE VAR1 char
WORK HAVE VAR2 char
WORK HAVE VAR3 char
You can use this metadata exactly the same as with any other dataset. In my instance I use the datastep itself as the loop over each observation. On the first observation I generate out the datastep code in the call execute, then for each observation I logically check type to see if it is char or num, then generate out the code specific for that type. On the last observation I finish off the generated code with a run; statement. The generated code is then executed after I finish the data _null_ step. So I am letting SAS use its metadata to programmatically generate my code for me! Really saves a lot on my poor old fingers!
When you transpose a mixture of character and numeric variables the numeric variables are converted to character using their associated formats. If you transpose back get the "same" data set back with all character variables. I like to left justify the as the converted numeric variables are generally right justified.
Hi,
You can use the following code. Note that you need to be careful however, the numeric could be in all kind of different formats so you may want to adjust the 8. to suit the data (am thinking especially about dates/ties which are also numeric).
data work.have;
length col1-col4 $20;
col1="GHGYT";col2="£$"; col3="jhkkjh"; col4="uyiyiy"; col5=1234; col6=456; col7=678; output;
run;
data _null_;
set sashelp.vcolumn (where=(libname="WORK" and MEMNAME="HAVE")) end=last;
if _n_=1 then call execute('data want;
set have;
array results{7} $20.;');
if type="char" then call execute(tranwrd(strip(name),"col","results")||'='||strip(name)||';');
else call execute(tranwrd(strip(name),"col","results")||'=strip(put('||strip(name)||',8.));');
if last then call execute('run;');
run;
VVALUE
RW9 wrote:
Note that you need to be careful however, the numeric could be in all kind of different formats so you may want to adjust the 8. to suit the data (am thinking especially about dates/ties which are also numeric).
Hi RW9,
Thanks for the speedy response. May i ask you to explain the following in your program if you don't mind at your own convenience please, I am not quite getting it.
data _null_;
set sashelp.vcolumn (where=(libname="WORK" and MEMNAME="HAVE")) end=last;/* why are you using sashelp.vcolumn?*/
if _n_=1 then call execute('data want;
set have;
array results{7} $20.;');
The rest i am pretty clear.
Many thanks indeed,
Charlotte
Yes, the SASHELP library contains metadata about the datasets and libraries SAS knows about. By pulling this over into my dataset I am getting that metadata to work with. So say I have a dataset HAVE in the WORK library and it has variable var1-3, the metadata on the table looks something like:
LIBNAME MEMNAME NAME TYPE...
WORK HAVE VAR1 char
WORK HAVE VAR2 char
WORK HAVE VAR3 char
You can use this metadata exactly the same as with any other dataset. In my instance I use the datastep itself as the loop over each observation. On the first observation I generate out the datastep code in the call execute, then for each observation I logically check type to see if it is char or num, then generate out the code specific for that type. On the last observation I finish off the generated code with a run; statement. The generated code is then executed after I finish the data _null_ step. So I am letting SAS use its metadata to programmatically generate my code for me! Really saves a lot on my poor old fingers!
HI all,
Each one's response is simply brilliant!!!. Thank you so much indeed
RW9,
Are they really poor old fingers?How old?(laughs) Doesn't seem like it as your response came in super speed.
Many thanks everyone and have a great day,
Charlotte from England
data have;
input @1 a @3 b $10. @14 c $5. @20 d;
cards;
1 2654321234 33456 4
;
%macro vars(dsn);
%let list=;
%let type=;
%let dsid=%sysfunc(open(&dsn));
%let cnt=%sysfunc(attrn(&dsid,nvars));
%do i = 1 %to &cnt;
%let list=&list %sysfunc(varname(&dsid,&i));
%let type=&type %sysfunc(vartype(&dsid,&i));
%end;
%let rc=%sysfunc(close(&dsid));
data want(drop=
%do i = 1 %to &cnt;
%let temp=%scan(&list,&i);
_&temp
%end;);
set &dsn(rename=(
%do i = 1 %to &cnt;
%let temp=%scan(&list,&i);
&temp=_&temp
%end;));
%do j = 1 %to &cnt;
%let temp=%scan(&list,&j);
/** Change C to N for numeric to character conversion **/
%if %scan(&type,&j) = N %then %do;
/** Also change INPUT to PUT for numeric to character **/
&temp=PUT(_&temp,8.);
%end;
%else %do;
&temp=_&temp;
%end;
%end;
run;
%mend vars;
%vars(one)
proc contents data=have;
run;
proc contents data=want;
run;
Have
Want
Once you got the number of numeric variables , SAS will convert it into character type variables automatically .
proc sql noprint ; select count(*) into : n separated by ' ' from dictionary.columns where libname='SASHELP' and memname='CLASS' and type='num'; quit; %put &n ; data want; set sashelp.class; array num{*} _numeric_; array char{*} $ 100 c1-c&n ; do i=1 to dim(num); char{i]=num{i}; end; run;
Xia Keshan
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.