topic Re: Convert all numeric variables to character in SAS Programming

Convert all numeric variables to character

lhsumdalum — Fri, 09 Mar 2018 18:29:25 GMT

I find a lot of great resources for converting character variables to numeric, but I don't find much in terms of doing the reverse. I'm interested in identifying all numeric variable types and converting them to character type. Moreover, I'm interested in keeping the same variable names.

Here is my thought process:

data convertnums;
	set sashelp.class; 
	array all_n[*] _numeric_ ;
	do i = 1 to dim(all_n); 
		all_n[i] = strip(put(all_n[i], $8.));  * Not working... decode; 
	end;
run; 

proc contents data = convertnums;   * Check to see if numeric type converted ;
run;

But I am getting a warning and am unable to convert numeric to character. Any thoughts or suggestions are welcome. Thanks!

Re: Convert all numeric variables to character

novinosrin — Fri, 09 Mar 2018 18:31:19 GMT

/*macro to convert all numeric to char*/
%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(your_dataset_name)

Re: Convert all numeric variables to character

lhsumdalum — Fri, 09 Mar 2018 18:48:31 GMT

Thanks novinosrin, I found that article here, but my question is more aimed at why my code is not functioning. Shouldn't the "put" function change the type from numeric?

Re: Convert all numeric variables to character

novinosrin — Fri, 09 Mar 2018 18:59:55 GMT

Back to basic,you need new assignment and can;t change existing mate i.e you need a new array

data HAVE;
  input ID YEAR Manager $  TURNOVER;
  drop TURNOVER;
datalines;
1 1990 a 0
1 1991 a 0
1 1992 b 1
1 1993 b 0
1 1994 b 0
1 1995 c 1
1 1996 c 0
1 1997 c 0
2 1993 d 0
2 1994 d 0
2 1995 d 0
2 1996 e 1
2 1997 e 0
2 1998 e 0
2 1999 e 0
2 2000 e 0
2 2001 e 0
;
run;



data convertnums;
	set sashelp.class; 
	array all_n[*] _numeric_ ;
	array t(3) $ _age _height _weight;
	do i = 1 to dim(all_n); 
		t[i] = strip(put(all_n[i], 8.));  
	end;
run; 
proc contents data = convertnums;   * Check to see if numeric type converted ;
run;

Re: Convert all numeric variables to character

novinosrin — Fri, 09 Mar 2018 19:01:30 GMT

at the time of compilation, sas reads the descriptive portion of the dataset and builds the PDV. Therefore, this rule will prevail.

Execution: merely deals with data portion

Re: Convert all numeric variables to character

SuryaKiran — Fri, 09 Mar 2018 20:10:42 GMT

Since your using the same variable names and when you have SET then formats are defined for those variables so, SAS automatically converts the data to numeric.

I would do something like this:

proc sql;
select STRIP(name)||"="||STRIP(name)||"_",STRIP(name)||"_",name INTO: rename separated by " ",:drop separated by " ",:need separated by " "
	from dictionary.columns
		where libname="SASHELP" and MEMNAME="CLASS"
	and type="num";
quit;
%put &need;

data convertnums(drop=&drop.);
/*format &need $8.;*/
	set sashelp.class(rename=(&rename.)); 
	array all_n[*] _numeric_ ;
	array need_Vars[*] $ &need.;
	
	do i = 1 to dim(all_n); 
		need_Vars[i] = strip(put(all_n[i], 8.));  * Not working... decode; 
	end;
run;

Re: Convert all numeric variables to character

Bhushan — Fri, 09 Mar 2018 19:37:32 GMT

this might help you

proc sql noprint;
select name into : NN1-: NN&sysmaxlong FROM DICTIONARY.COLUMNS where libname="SASHELP" and MEMNAME="ASSCMGR" and Type="num";
%let last=&sqlobs;
quit;
Options mprint mlogic merror;

%macro Z;
data want;
set sashelp.ASSCMGR;
%do I=1 %to &last;
&&NN&i.._New=put(&&NN&i,Best32.);
%end;
run;
%mend;
%z;

Re: Convert all numeric variables to character

Bhushan — Fri, 09 Mar 2018 19:39:55 GMT

proc sql noprint;
select name into : NN1-: NN&sysmaxlong FROM DICTIONARY.COLUMNS where libname="SASHELP" and MEMNAME="ASSCMGR" and Type="num";
%let last=&sqlobs;
quit;
Options mprint mlogic merror;

%macro Z;
data want;
set sashelp.ASSCMGR;
%do I=1 %to &last;
&&NN&i.._New=put(&&NN&i,Best32.);
%end;
run;
%mend;
%z;

Re: Convert all numeric variables to character

Tom — Fri, 09 Mar 2018 22:48:03 GMT

One easy way is to use PROC TRANSPOSE twice.

Two things you need to make sure you have.

1) A way to uniquely identify the original rows.

2) At least one character variable.

Let's make a little test dataset that has a number and a date variable.

data test ;
  _row_+1;
  input name :$20. age date :date9. ;
  format date date9. ;
cards;
Sam 20 01JAN2018
Julie 30 30DEC2016
;

So in that dataset _ROW_ is my unique identifier. So now let's transpose it. Notice we will need to exclude _ROW_ from the resulting data. Then transpose it back.

proc transpose data=test
   out=middle (where=(lowcase(_name_) ne '_row_'))
;
  by _row_;
  var _all_;
run;

proc transpose data=middle out=want (drop=_name_);
  by _row_;
  var col1 ;
run;

Let's see what happened.

proc contents data=want; run;
proc print data=want; run;

The CONTENTS Procedure

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

1    _row_       Num       8
3    age         Char     20
4    date        Char     20
2    name        Char     20

Obs    _row_    name     age      date

 1       1      Sam      20     01JAN2018
 2       2      Julie    30     30DEC2016

If you don't know if you have an id variable or a character variable then you could just create one first.

data to_transpose ;
  _row_ + 1;
  length _charvar_ $12 ;
  set have;
run;

And then use that as the source table to start with. You can later drop the _ROW_ and _CHARVAR_ variables.

Also note that it will typically right align the converted numeric variables. If you would rather have them left aligned add a step between the proc transpose steps to modify the "middle" dataset.

data middle;
  set middle;
  col1=left(col1);
run;