Date: Fri, 2 Oct 2009 18:43:20 -0400
Reply-To: Arthur Tabachneck <art297@NETSCAPE.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Arthur Tabachneck <art297@NETSCAPE.NET>
Subject: Re: how to convert a num. item to char in proc sql?
Demin,
In your original post you didn't indicate that you were working with
decimals.
Try the following:
data have_num;
input oldvalue;
cards;
0.8595
0.8998
0.4467
0.8504
0.3967
0.550
;
proc sql;
create table have_char as
select left(put(oldvalue,8.4)) as newvalue
from have_num;
quit;
PROC EXPORT DATA= WORK.have_char
OUTFILE= "c:\char.xls"
DBMS=EXCEL2000 REPLACE;
SHEET="data";
RUN;
I end up with a spreadsheet that looks like:
newvalue
0.8595
0.8998
0.4467
0.8504
0.3967
0.5500
Is that what you want your file to look like?
Art
-------
On Fri, 2 Oct 2009 06:48:39 -0700, demin <qdmiris@GMAIL.COM> wrote:
>On Oct 1, 8:03 pm, "Lou" <lpog...@hotmail.com> wrote:
>> ""Terjeson, Mark"" <Mterje...@RUSSELL.COM> wrote in message
>>
>>
news:16FD64291482A34F995D2AF14A5C932C07F180CC@MAIL002.prod.ds.russell.com...
>>
>> > Hi Demin,
>>
>> > You are very close.
>>
>> > the PUT function converts numeric to character
>> > the INPUT function converts character to numeric
>>
>> You're also very close. The PUT function **always** returns a character
>> value (it can convert one character value to another character value as
well
>> as convert a numeric value to a character value). The INPUT function
>> returns either a character or numeric value, depending on the informat
used.
>>
>> nothing new below, included for reference only
>>
>>
>>
>>
>>
>> > input(oldvalue,$8.) as newvalue
>>
>> > is correct if you are converting numeric to character.
>>
>> > use
>>
>> > put(oldvalue,8.) as newvalue
>>
>> > to convert numeric to character.
>>
>> > Now for a gotcha: if you use
>>
>> > put(value,$8.) as value
>>
>> > you will still end up with a numeric variable
>> > even if the PUT converts it to character. The
>> > problem of using the SAME variable name is that
>> > once the variable type has been established by
>> > the compiler looking through all of the code
>> > prior to execution, what will happen is that
>> > the PUT() function will indeed convert to string
>> > but then the assignment(=) task is next and it
>> > will see that the new temporary intermediate
>> > string value will get converted (again) to the
>> > preexisting destination variable type. And in
>> > your case it ends up as numeric all over again.
>> > So check and use/re-use variable names accordingly.
>>
>> > Hope this is helpful.
>>
>> > Mark Terjeson
>> > Investment Business Intelligence
>> > Investment Management & Research
>> > Russell Investments
>> > 253-439-2367
>>
>> > Russell
>> > Global Leaders in Multi-Manager Investing
>>
>> > -----Original Message-----
>> > From: SAS(r) Discussion [mailto:SA...@LISTSERV.UGA.EDU] On Behalf Of
>> > demin
>> > Sent: Thursday, October 01, 2009 11:38 AM
>> > To: SA...@LISTSERV.UGA.EDU
>> > Subject: how to convert a num. item to char in proc sql?
>>
>> > how to convert a num. item to char in proc sql?
>>
>> > I tried put, input, but doesn't work:
>> > proc sql;
>> > create view myVW as select item,input(value,$8.) as value from
>> > mytable
>> > where statistic='aaa';
>> > quit;
>>
>> > thanks.- Hide quoted text -
>>
>> - Show quoted text -
>
>Sorry, guys, my data wont work by using put:
>ODS HTML FILE='...\a.xls';
>proc sql;
> select item, round(a,.001) format = 5.3 from exact
> order by substr(item,5,1),input(substr(item,7,2),BEST12.);
>quit;
>oDS HTML CLOSE;
>
>Because when export to excel, the data like 0.550, 0.5 will miss the 0
>and become 0.55, 0.5, so I tried to convert to char and then export.
>but when I tried to use 'put', the result will change to 1 digit(0 or
>1), but this is not correct. I don't know if there is till has
>solution, otherwise, I only can do some change after export to excel.
>data have_num;
> input oldvalue;
> cards;
>0.8595
>0.8998
>0.4467
>0.8504
>0.3967
>;
>
>proc sql;
> select left(put(oldvalue,8.)) as newvalue
> from have_num;
>run;
| 
