topic Re: Convert numeric to character in a query in Developers

Convert numeric to character in a query

deleted_user — Tue, 29 May 2007 13:50:59 GMT

I've got a query that creates a new output table field by concatenating a variable to an input table field as follows:

"&threespaces" || EMPLOYEE.PERSON_UID,9) as NEWKEY

However, when I run my query, it complains because the EMPLOYEE_PERSON_UID is numeric. How might I convert this field so the query doesn't complain? I am aware that SAS usually ignores spaces or blanks at the beginning of a field, however, this field needs the "threespaces" at its beginning.

Thanks in advance!

Re: Convert numeric to character in a query

Cynthia_sas — Tue, 29 May 2007 15:39:33 GMT

Hi:
SAS is really picky about the difference between character and numeric variables -- and spaces are not valid in a number. I assume that you want the NEWKEY variable to be character. The key is doing your concatenate with a PUT function to turn the ID variable into a character value for the concatenate operation. Then the format=$char12. will tell SAS to respect the leading blanks in the new field--which will be a CHARACTER variable.
Good luck!
cynthia
[pre]
data employee;
infile datalines;
input name $ PERSON_UID;
return;
datalines;
alma 123456789
bob 234567890
carl 345678901
dave 456789012
;
run;

proc print data=employee;
title 'person_uid is numeric';
run;

proc sql;
create table testit as
select name, employee.person_uid,
' '||left(put(employee.person_uid,9.)) as NEWKEY format=$char12.
from work.employee as employee;
quit;

proc print data=testit;
title 'NEWKEY is a character variable';
title2 'So the PUT function was used to turn a numeric var into a character value for the concat.';
run;

[/pre]

Re: Convert numeric to character in a query

deleted_user — Tue, 29 May 2007 17:16:34 GMT

Thanks, Cynthia,

Still being so darn new to all that SAS has to offer, I wasn't sure if the PUT code could be used inside a query. Your advice, as always, comes in handy!

Re: Convert numeric to character in a query

Cynthia_sas — Tue, 29 May 2007 18:17:42 GMT

Hi!
You have soooo much fun to look forward to. Between the SAS formats and the SAS functions you can do a lot of very cool stuff:
Formats and Informats:
http://www2.sas.com/proceedings/sugi25/25/aa/25p002.pdf
http://www2.sas.com/proceedings/sugi26/p085-26.pdf
http://www.kellogg.northwestern.edu/rc/workshops/papers/finley_sugi25.pdf
http://www8.sas.com/scholars/05/PREVIOUS/2001_200.4/2004_MOR/Proceed/_2004/IntroductionToSAS/IN06-Droogendyk.pdf

Functions:
http://www8.sas.com/scholars/05/SESUG_05/Proceedings/2005/Intro2SAS/IN08_05.PDF

http://www2.sas.com/proceedings/sugi29/255-29.pdf

http://www.hasug.org/newsletters/hasug200511/Muir_Character_Function_Fun.pdf

http://www.uchsc.edu/coho/SAS_Uers/FavoriteFunctions.doc

The SAS documentation is quite good on the subject of functions and formats/informats. The SAS functions and odd behavior in PROC SQL are described here:
http://support.sas.com/techsup/unotes/SN/014/014659.html

and here's a note about functions and PROC SQL:
http://support.sas.com/ctx/samples/index.jsp?sid=819

Have fun!
cynthia

Re: Convert numeric to character in a query

deleted_user — Fri, 01 Jun 2007 20:40:23 GMT

Thanks for the information, Cynthia. I truly appreciate it.