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

[Mirisage]

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

[PGStats · Accepted solution]

ii) yes, you may use :

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

in the SQL query.

PG

[PGStats]

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;

PG

[SGB]

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;

[Mirisage]

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

[SGB]

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.

BG

[Mirisage]

Thank you very much SBG.

Mirisage

[beatledodger]

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.