topic Re: Convert numeric var to char in SAS Programming

Convert numeric var to char

oht — Thu, 16 Aug 2012 16:49:13 GMT

Hi everyone.

This seems to be a simple request but I got stuck and need some help . I have a datasets with 756 variables and some of them are numeric field type=1. Is there any already make routine that I can convert all the numeric var into character var ?

Thanks for all your help

Re: Convert numeric var to char

HK_EndeavourForever — Thu, 16 Aug 2012 17:00:05 GMT

Hello Oht,

Do you use any layout to read the SAS dataset with 756 variables in your sas program?

Thank You!

Re: Convert numeric var to char

Linlin — Thu, 16 Aug 2012 17:13:34 GMT

example (red part must be upcase):

data have;

input id $ sex $ a b c d e ;

cards;

1 2 11 22 33 44 55

;

proc sql noprint;

select catx(' ','put(',name,',8.) as',name) into : list1 separated by ','

from dictionary.columns

where libname='WORK' and memname='HAVE' and type ='num' ;

select name into : list2 separated by ','

from dictionary.columns

where libname='WORK' and memname='HAVE' and type ='char' ;

create table want as

select &list1,&list2 from have;

quit;

proc contents data=want;run;

Re: Convert numeric var to char

oht — Thu, 16 Aug 2012 17:17:37 GMT

I inherated the SAS dataset without layout. The proc contents will get the layout. The problem is when I writing the data out to a flat file. What kind of field length should I define these numeric values. If I use the default BEST12. and the value happened to be more that 12 then SAS will convert these number into scientific lile 123E2 and I try to avoid this happening. Since the length function cannot determie the true length of the numeric field, the other alternative is to convert all of them into char and use the lengh func to determine the max length of each field. Do you know of any macro that can convert the num var to char ?

Thanks

Re: Convert numeric var to char

ArtC — Thu, 16 Aug 2012 17:32:51 GMT

The following macro, which I found in Appendix 1 a macro book**, does the conversion that you are looking for. It has the advantage of maintaining variable names and labels and uses any numeric formats (such as dates) in the conversion.

**********************;
***A1.6.4
***%ALLCHAR
**********************;
%macro allchar(dsn);
* Determine the numeric vars in &dsn;
proc contents data=&dsn out=cont noprint;
run;

* Create the macro variables for each numeric var;
data _null_;;
set cont(keep=name type format formatl formatd label);
length fmt $15;
where type=1;

* Count the numeric vars and save the total number;
i+1;
ii=left(put(i,3.));
call symput('n',ii);

* create a format string;
fmt = 'best.';
if format ne ' ' then fmt = trim(format)
||trim(left(put(formatl,3.)))
||'.'||left(put(formatd,3.));
fmt = compress(fmt);
call symput('fmt'||ii,fmt);

* Save the variable name;
call symput('name'||ii,name);

* Save the label for this variable;
if label = ' ' then label = name;
call symput('label'||ii,label);
run;

* Establish a data set with only character variables;
* &n       number of numeric variables in &dsn;
* __aa&i   temporary var to hold numeric values;
* &&name&i name of the variable to covert from numeric;
*
* The numeric value of &name1 is stored in __aa1
* by renaming the variable in the SET statement. __aa1
* is then converted to character and stored in the
* 'new' variable &name1 in the data set CHARONLY.
* ;
data charonly (drop=
   %* Drop the temp. vars used to hold numeric values;
   %do i=1 %to &n;
      __aa&i
   %end;
    );
length
   %* Establish the vars as character;
   %do i=1 %to &n;
      &&name&i
   %end;
    $8;

set &dsn (rename=(
   %* Rename the incoming numeric var to a temp name;
   %* This allows the reuse of the variables name;
   %do i=1 %to &n;
      &&name&i=__aa&i
   %end;
   ));

   * Convert the numeric values to character;
   %do i=1 %to &n;
      &&name&i = left(put(__aa&i,&&fmt&i));
      label &&name&i = "&&label&i";
   %end;
run;

proc contents data=charonly;
proc print data=charonly;
run;
%mend allchar;

%allchar(sasclass.biomass)

** Appendix 1 of Carpenter's Complete Guide to the SAS Macro Language, 2nd Edition. Used with the permission of the author.

Re: Convert numeric var to char

Astounding — Thu, 16 Aug 2012 17:42:49 GMT

So why use best12.? This would be working code:

put var1 : best16. +(-1) '|' var2 : best16. +(-1) '|' var3 best16.;

The colon indicates that values get written in scanning mode (no leading blanks, one trailing blank). The +(-1) repositions the pointer back a space, so the trailing blank gets overwritten with the delimiter.

Good luck.

Re: Convert numeric var to char

oht — Thu, 16 Aug 2012 17:47:02 GMT

WOW, thanks for all your quick responses. I thinks I have enough info to go on..

Again, thanks for all your help. Really make me feel good to be a SAS user !

Re: Convert numeric var to char

art297 — Thu, 16 Aug 2012 18:36:04 GMT

Then give formal credit to those who helped you by marking their posts as either being the correct answer or at least having been helpful.

Please don't mark my post either way as I didn't provide anything toward this thread.

Re: Convert numeric var to char

Peter_C — Thu, 16 Aug 2012 22:03:25 GMT

convert all numerics to character with

put _numeric_ ;

default formats will be used, the list will be blank delimited.

If that suits, but you want to have them all in a string, use

length collection $32767 ;

put; *clear the buffer;

format _numeric_ 32. ;

put _numeric_ @ ;

collection = _file_ ;

_file_ = ' ' ;

put @1 @ ;

Re: Convert numeric var to char

oht — Fri, 17 Aug 2012 12:30:05 GMT

Hi Peter,

Thanks for your interesting idea. However, when I tested your code, there is an error.

23 data have;

24 input id $ sex $ a b c d e ;

25 cards;

NOTE: The data set WORK.HAVE has 1 observations and 7 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

27 ;

28 data test;

29 length collection $32767 ;

30 set have ;

31 put; *clear the buffer;

32 put _numeric_ @ ;

ERROR: Cannot use _numeric_ as a variable name.

33 collection = _file_ ;

34 _file_ = ' ' ;

35 put @1 @ ;

36 run;

NOTE: The SAS System stopped processing this step because of errors.

Do you know what cause this error ?

Thanks

Re: Convert numeric var to char

oht — Fri, 17 Aug 2012 13:34:31 GMT

Hi LinLin,

Your code works out very good. The only problem I have is the final dataset WANT has all the variables $200. Is there any way for the char variable to maintain their original format length ?

Thanks

Re: Convert numeric var to char

art297 — Fri, 17 Aug 2012 13:37:24 GMT

The cause of the error is described at: http://www.sascommunity.org/wiki/PUT_Statement_with_ALL

You can get around it by using:

data have;

input id $ sex $ a b c d e ;

cards;

1 M 1 2 3 4 5

;

data test;

length collection $32767 ;

set have ;

put; *clear the buffer;

put (_numeric_) (+0) @ ;

collection = _file_ ;

_file_ = ' ' ;

put @1 @ ;

run;

Re: Convert numeric var to char

MikeZdeb — Fri, 17 Aug 2012 16:11:05 GMT

hi ... I think that you can get away with fewer statements (you'll end up with one record written to the LOG) ... COLLECTION ends up with the same length as _FILE_ and that defaults to 32767 so maybe a LENGTH statement is appropriate ...

data have;

input (id sex) (:$1.) a b c d e @@;

cards;

1 M 1 2 3 4 5 2 F 9 9 9 9 9 3 M 8 8 8 8 8 1 M 1 2 3 4 5

2 F 9 9 9 9 9 3 M 8 8 8 8 8 3 M 8 8 8 8 8 4 Q 9 9 9 9 9

;

data test;

set have;

put @1 (_numeric_) (:) @ ;

collection = _file_ ;

run;

LOG ...

441 data test;

442 set have;

443 put @1 (_numeric_) (:) @ ;

444 collection = _file_ ;

445 run;

9 9 9 9 9

NOTE: There were 8 observations read from the data set WORK.HAVE.

NOTE: The data set WORK.TEST has 8 observations and 8 variables.

data set ...

id sex a b c d e collection

1 M 1 2 3 4 5 1 2 3 4 5

2 F 9 9 9 9 9 9 9 9 9 9

3 M 8 8 8 8 8 8 8 8 8 8

1 M 1 2 3 4 5 1 2 3 4 5

2 F 9 9 9 9 9 9 9 9 9 9

3 M 8 8 8 8 8 8 8 8 8 8

4 Q 9 9 9 9 9 9 9 9 9 9

Re: Convert numeric var to char

Linlin — Fri, 17 Aug 2012 16:16:54 GMT

Hi,

I don't know how to maintain the original length. You can assign a fixed length to all the variables.

data have;

input id $ sex $ a b c d e ;

cards;

1 2 11 22 33 44 55

;

proc sql noprint;

select cat('put(',strip(name),',8.) as ',strip(name), ' length=20') into : list1 separated by ','

from dictionary.columns

where libname='WORK' and memname='HAVE' and type ='num' ;

select name into : list2 separated by ','

from dictionary.columns

where libname='WORK' and memname='HAVE' and type ='char' ;

create table want as

select &list1,&list2 from have;

quit;

proc contents data=want;run;

# Variable Type Len

1 a Char 20

2 b Char 20

3 c Char 20

4 d Char 20

5 e Char 20

6 id Char 8

7 sex Char 8

Re: Convert numeric var to char

oht — Fri, 17 Aug 2012 17:04:46 GMT

Thanks Mike and Peter,

This is a very interesting idea. VERY COOL ! Even I don't use for this apps but definitely save for future reference..

Re: Convert numeric var to char

oht — Fri, 17 Aug 2012 17:07:20 GMT

Hi LinLin,

I find some other tedious way of getting back the original attr. It works.

Thanks for all your help.

Re: Convert numeric var to char

oht — Fri, 17 Aug 2012 17:19:25 GMT

Hi Mike and Peter,

One more question, if I do not want the number to combine in a single variable collection and want to convert all the variables from numeric to char, how would i apply this _numeric_ to get that that done ?

Thanks

Re: Convert numeric var to char

MikeZdeb — Fri, 17 Aug 2012 19:21:10 GMT

hi ... one way (using an idea based on a recent PGSTATS posting) ...

data have;

input (id sex) (:$1.) a b c d e @@;

cards;

1 M 1 2 3 4 5 2 F 9 9 9 9 9 3 M 8 8 8 8 8 1 M 1 2 3 4 5

2 F 9 9 9 9 9 3 M 8 8 8 8 8 3 M 8 8 8 8 8 4 Q 9 9 9 9 9

;

data test;

input @;

do until (done);

set have end=done;

put @1 (_numeric_) (:) @ ;

_infile_ = _file_;

input @1 (x1-x5) (:$1.) @;

output;

end;

datalines;

;

Alphabetic List of Variables and Attributes

# Variable Type Len

3 a Num 8

4 b Num 8

5 c Num 8

6 d Num 8

7 e Num 8

1 id Char 1

2 sex Char 1

8 x1 Char 1

9 x2 Char 1

10 x3 Char 1

11 x4 Char 1

12 x5 Char 1

id sex a b c d e x1 x2 x3 x4 x5

1 M 1 2 3 4 5 1 2 3 4 5

2 F 9 9 9 9 9 9 9 9 9 9

3 M 8 8 8 8 8 8 8 8 8 8

1 M 1 2 3 4 5 1 2 3 4 5

2 F 9 9 9 9 9 9 9 9 9 9

3 M 8 8 8 8 8 8 8 8 8 8

4 Q 9 9 9 9 9 9 9 9 9 9

Re: Convert numeric var to char

PGStats — Fri, 17 Aug 2012 19:45:13 GMT

Wow Mike!

_infile_ = _file_;

Do not show this to minors! :smileygrin:

Re: Convert numeric var to char

MikeZdeb — Fri, 17 Aug 2012 19:53:53 GMT

hi ... when I saw your idea about a week ago, I though that it might be a neat way to recode a lot of variables (possibly an alternative to an array and a loop)...

proc format;

value x 10 = '0' other='1';

run;

data x;

input x1-x10;

datalines;

10 20 30 10 10 10 10 10 10 88

10 10 10 11 12 10 13 15 16 99

99 99 99 99 99 99 99 99 10 10

;

data y;

input @;

do until(done);

set x end=done;

put @1 (x1-x10) (x.) @;

_infile_ = _file_;

input @1 (x1-x10) (1.) @;

output;

end;

datalines;

dummy

;

x1 x2 x3 x4 x5 x6 x7 x8 x9 x10

0 1 1 0 0 0 0 0 0 1

0 0 0 1 1 0 1 1 1 1

1 1 1 1 1 1 1 1 0 0