BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lhsumdalum
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
novinosrin
Tourmaline | Level 20
/*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)
lhsumdalum
Obsidian | Level 7

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?

novinosrin
Tourmaline | Level 20

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;

novinosrin
Tourmaline | Level 20

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

SuryaKiran
Meteorite | Level 14

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
Bhushan
Calcite | Level 5

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;

,

 

Bhushan
Calcite | Level 5


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;

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 17639 views
  • 6 likes
  • 5 in conversation