sum and group by

[Solly7]

Hi, i have extracted characters(from 6th character till the last one) from numeric variable(account_number) and stored the results in new variable Acct_no from below logic, after that i did summation of trns_amt & trns_volume variables and grouped by the new variable Acct_no .

before using substring i had 16 digits on account_number variable(e,g 4000061234567890) and my new variable Acct_no had the result 61234567890 which is what i wanted.

Now i am having an issue on my summary data, the variable Acct_no excludes the first digit(which is 6 in this instance), for example it returns(1234567890) and omit 6

proc sql;
   create table acct_extract as
   select *,
       SUBSTR(put(CH_ACCT_NO,best32.-L),6) as Acct_no
   from ccpos_trns
;quit;

PROC SQL ;
 create table credit_total_spnt as
 select Acct_no,
        sum(TRNS_AMT) as Total_Amt,
		sum(TRNS_VOLUME) as Total_Trns
 from acct_extract
 group by Acct_no
 ;quit;

[PaigeMiller · Accepted solution]

I can't duplicate your problem. Please provide a portion of your real data which illustrates the problem, as working SAS data step code (Instructions), and not in any other format.

data have;
    CH_ACCT_NO=4000061234567890;
    trns_amt=7;
    trns_volume=120;
run;

proc sql;
   create table acct_extract as
   select *,
       SUBSTR(put(CH_ACCT_NO,best32.-L),6) as Acct_no
   from have
;quit;

PROC SQL ;
 create table credit_total_spnt as
 select Acct_no,
        sum(TRNS_AMT) as Total_Amt,
		sum(TRNS_VOLUME) as Total_Trns
 from acct_extract
 group by Acct_no
 ;quit;

[Kurt_Bremser]

If CH_ACCT_NO is stored as a number, this needs to be rectified first, at the point where the data is read into the SAS environment. Account "numbers" are not numbers used for calculations, they are just codes, which m7st be stored as character. In the moment they exceed 15 decimal digits, the limits of numerical precision will kick in and lead to unpredictable and wrong results.