DATA Step, Macro, Functions and more

Convert all numeric variables to character

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Convert all numeric variables to character

[ Edited ]

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!


Accepted Solutions
Solution
‎03-11-2018 09:42 PM
Super User
Super User
Posts: 8,279

Re: Convert all numeric variables to character

[ Edited ]
Posted in reply to lhsumdalum

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;

View solution in original post


All Replies
Super User
Posts: 2,061

Re: Convert all numeric variables to character

Posted in reply to lhsumdalum
/*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)
Occasional Contributor
Posts: 17

Re: Convert all numeric variables to character

Posted in reply to novinosrin

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?

Super User
Posts: 2,061

Re: Convert all numeric variables to character

Posted in reply to lhsumdalum

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;

Super User
Posts: 2,061

Re: Convert all numeric variables to character

Posted in reply to novinosrin

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

PROC Star
Posts: 629

Re: Convert all numeric variables to character

[ Edited ]
Posted in reply to lhsumdalum

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; 
Thanks,
Suryakiran
Occasional Contributor
Posts: 9

Re: Convert all numeric variables to character

Posted in reply to SuryaKiran

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;

,

 

Occasional Contributor
Posts: 9

Re: Convert all numeric variables to character

Posted in reply to lhsumdalum


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;

Solution
‎03-11-2018 09:42 PM
Super User
Super User
Posts: 8,279

Re: Convert all numeric variables to character

[ Edited ]
Posted in reply to lhsumdalum

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 612 views
  • 1 like
  • 5 in conversation