Help needed in changing variable from Numeric to Character?

[CharlotteCain]

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

[data_null__]

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.

proc transpose data=sashelp.class out=tclass;
   by name;
   var age height sex weight;
   run;
data charclass;
   set charclass;
   col1 = left(col1);
   run;
proc transpose data=tclass out=charclass(drop=_name_);
   by name;
   var col1;
   run;

proc contents;
   run;
proc print;
   run;

[RW9]

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;

[data_null__]

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

[CharlotteCain]

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

[RW9 · Accepted solution]

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!

[CharlotteCain]

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

[Hima]

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

[Ksharp]

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