topic Re: Can we convert numeric variable into a character var in the sql extraction process ? in SAS Procedures

Can we convert numeric variable into a character var in the sql extraction process ?

Mirisage — Tue, 31 Jul 2012 17:26:03 GMT

Hi SAS Forum,

I have the attached data set.

I have got the sum of variables named “principal” and “write_off_total” by account_number using below sql code.

(Art and Tish have helped me to introduce date formatting in this sql code).

proc sql;

create table want as

select

min(bank_number) as bank_number ,

min (ACCOUNT_NUMBER) as ACCOUNT_NUMBER ,

min(country) as country ,

min(branch_number) as branch_number ,

min(currency_code) as currency_code ,

max (POST_DATE) as POST_DATE Format=date9. ,

max (EFFECTIVE_DATE) as EFFECTIVE_DATE Format=date9. ,

sum (PRINCIPAL) as sum_PRINCIPAL ,

sum (Write_off_Total) as net_Write_off_Total

from b.post_this_data

group by account_number

;

quit;

Background:

Account_number and Branch_number are numerics.

I need to convert them to character variables.

What I have done, I have converted them using following two steps after the above sql extraction.

data want;

set want;

char_account_number = put(account_number, $15.) ;

drop account_number ;

rename char_account_number=account_number ;

run;

data want;

set want;

char_branch_number = put(branch_number, $7.) ;

drop branch_number ;

rename char_branch_number=branch_number ;

run;

Questions:

i). I wonder if this procedure is correct ? I noted the value moved to middle of cell in “account_number” variable and moved to the left hand corner of the cell in “Branch_number” variable after this conversion (suspicious!).

ii). Can we do this conversion at the sql extraction process itself?

Thanks

Mirisage

Re: Can we convert numeric variable into a character var in the sql extraction process ?

PGStats — Tue, 31 Jul 2012 17:36:23 GMT

ii) yes, you may use :

put(account_number, 15.) as account_number, put(min(branch_number),7.) as branch_number,

in the SQL query.

Re: Can we convert numeric variable into a character var in the sql extraction process ?

PGStats — Tue, 31 Jul 2012 17:51:02 GMT

I am foreign to the realm of banking, but somehow, I think this query should be like this:

proc sql;

create table want as

select

country, bank_number,

put(branch_number, 7.) as branch_number,

put(account_number, 15.) as account_number,

currency_code,

max (POST_DATE) as POST_DATE Format=date9. ,

max (EFFECTIVE_DATE) as EFFECTIVE_DATE Format=date9. ,

sum (PRINCIPAL) as sum_PRINCIPAL ,

sum (Write_off_Total) as net_Write_off_Total

from b.post_this_data

group by country, bank_number, branch_number, account_number, currency_code;

quit;

Re: Can we convert numeric variable into a character var in the sql extraction process ?

SGB — Tue, 31 Jul 2012 17:53:55 GMT

You can use the following code, And you can avoid the two data steps after the SQL.

-use PUT to convert the numeric to Character

-LEFT to left align the character values

proc sql;

create table want as

select

min(bank_number) as bank_number ,

left((put(min (ACCOUNT_NUMBER),7.)) as ACCOUNT_NUMBER ,

min(country) as country ,

left((put(min(branch_number),15.)) as branch_number ,

min(currency_code) as currency_code ,

max (POST_DATE) as POST_DATE Format=date9. ,

max (EFFECTIVE_DATE) as EFFECTIVE_DATE Format=date9. ,

sum (PRINCIPAL) as sum_PRINCIPAL ,

sum (Write_off_Total) as net_Write_off_Total

from b.post_this_data

group by account_number

;

quit;

Re: Can we convert numeric variable into a character var in the sql extraction process ?

Mirisage — Tue, 31 Jul 2012 19:25:14 GMT

Hi PGStats and SGB,

Many thanks for both of you.

This is great!

Hi SGB,

Would inclusion of the the "Left" statement intentionally hurt joinning my tables with "ACCOUNT_NUMBER" as the joining variable?

Why I am asking this is I have not intentionally left aligned this variable in my other tables and I need to do several joins with those tables.

Thanks

Mirisage

Re: Can we convert numeric variable into a character var in the sql extraction process ?

SGB — Wed, 01 Aug 2012 17:19:16 GMT

Hi Mirisage!

You have mentioned the following..

I noted the value moved to middle of cell in “account_number” variable and moved to the left hand corner of the cell in “Branch_number” variable after this conversion (suspicious!).

Hence I suspected that there might be leading blanks in account_number, and hence included left.

Left removes the leading blanks and left aligns the character.

The account_number with and without leading blanks would be treated as different values for the variable while merging.
Hope this helps.

Re: Can we convert numeric variable into a character var in the sql extraction process ?

Mirisage — Wed, 08 Aug 2012 01:54:46 GMT

Thank you very much SBG.

Mirisage

Re: Can we convert numeric variable into a character var in the sql extraction process ?

beatledodger — Fri, 15 Apr 2016 22:18:33 GMT

Thanks so much for this:

data want;

set want;

char_account_number = put(account_number, $15.) ;

drop account_number ;

rename char_account_number=account_number ;

run;

I'm sure that there are other ways to accomplish this, but this certainly worked for me. I am posting SAS data to a SQL server and SAS likes to push dates as numeric. I've tried various versions of the PUT statement, FORMAT, etc. But this did it. Thanks again.