BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

7 REPLIES 7
PGStats
Opal | Level 21

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

PG
PGStats
Opal | Level 21

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

PG
SGB
Obsidian | Level 7 SGB
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7 SGB
Obsidian | Level 7

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
Obsidian | Level 7

Thank you very much SBG.

Mirisage

beatledodger
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 287164 views
  • 16 likes
  • 4 in conversation