Solved
Contributor
Posts: 60

# calculating amounts by roll up Supplementary acc to basic acc

Hi,

Need programming advice on the above issue, i have variable as below:-

ACC_NO

SUB_ACCNO

PRODUCT

ACC_PLUS_PRODUCT(combination of   ACC_NO and PRODUCT)

CATEGORY (BASIC OR SUPPLMENTARY)

AMT

MY DATA:

11111 | 12345 | GOLD | 11111GOLD | B | 50
11111 | 24895 | BLACK | 11111BLACK | B | 25
11111 | 24445 | BLACK | 11111BLACK | B | 75

11111 | 27894 | SILVER | 11111SILVER | B | 140
11111 | 23456 | SILVER | 11111SILVER | S | 70
11111 | 86475 | GOLD | 11111GOLD | S | 130
22222 | 34567 | SILVER | 22222SILVER | B | 100
22222 | 12458 | GOLD | 22222GOLD | B | 50
22222 | 12557 | GOLD | 22222GOLD | S | 100
33333 | 23785 | GOLD | 33333GOLD | B | 75
33333 | 12968 | SILVER | 33333SILVER | B | 110
33333 | 12789 | SILVER | 33333SILVER | S | 110

How to do i get programming code to perform outcome as per below:-

11111 | 12345 | GOLD | 11111GOLD | B | 180
11111 | 24895 | BLACK | 11111BLACK | B | 25
11111 | 24445 | BLACK | 11111BLACK | B | 75
11111 | 27894 | SILVER | 11111SILVER | B | 210
22222 | 34567 | SILVER | 22222SILVER | B | 100
22222 | 12458 | GOLD | 22222GOLD | B | 150
33333 | 23785 | GOLD | 33333GOLD | B | 75
33333 | 12968 | SILVER | 33333SILVER | B | 220

Best Regards

Accepted Solutions
Solution
‎06-23-2017 10:51 AM
Posts: 1,147

## Re: calculating amounts by roll up Supplementary acc to basic acc

[ Edited ]

The below sql code should ovecome the issue, please try and let me know

``````
proc sql;
create table want(drop=amt sort sum) as select a.*, b.sum, case when sum>1 then sum(amt) else amt end as sumamt2,case when category='B' then 1 else 2 end as sort from have as a left join (select distinct ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, sum(ord) as sum from (select distinct ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, category,case when category='B' then 1 else 2 end as ord from have
group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT,category) group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT) as b on a.ACC_NO=b.ACC_NO and a.PRODUCT=b.PRODUCT and a.ACC_PLUS_PRODUCT=b.ACC_PLUS_PRODUCT
group by a.ACC_NO, a.PRODUCT, a.ACC_PLUS_PRODUCT having min(sort)=sort;
quit;
``````

Thanks,
Jag

All Replies
Contributor
Posts: 60

## Re: calculating amounts by roll up Supplementary acc to basic acc

attached my code as well, but this code it cumulative the same ACC_PLUS_PRODUCT(in this case 11111BLACK) into one. i just need sup acc to be roll up and no of acc for basic no change.

``````data have;
infile datalines dlm='|';
input  ACC_NO    SUB_ACCNO   PRODUCT \$  ACC_PLUS_PRODUCT :\$12.   CATEGORY \$    AMT;
datalines;
11111 | 12345 | GOLD | 11111GOLD | B | 50
11111 | 24895 | BLACK | 11111BLACK | B | 25
11111 | 24445 | BLACK | 11111BLACK | B | 75
11111 | 27894 | SILVER | 11111SILVER | B | 140
11111 | 23456 | SILVER | 11111SILVER | S | 70
11111 | 86475 | GOLD | 11111GOLD | S | 130
22222 | 34567 | SILVER | 22222SILVER | B | 100
22222 | 12458 | GOLD | 22222GOLD | B | 50
22222 | 12557 | GOLD | 22222GOLD | S | 100
33333 | 23785 | GOLD | 33333GOLD | B | 75
33333 | 12968 | SILVER | 33333SILVER | B | 110
33333 | 12789 | SILVER | 33333SILVER | S | 110
;

proc sort data=have;
by ACC_PLUS_PRODUCT DESCENDING CATEGORY;
run;

data want;
set have;
by ACC_PLUS_PRODUCT DESCENDING CATEGORY;
retain SG_AMT;
IF first.ACC_PLUS_PRODUCT then
SG_AMT = AMT;
ELSE
SG_AMT = SG_AMT + AMT;
IF LAST.ACC_PLUS_PRODUCT THEN OUTPUT;
RUN;``````
Super User
Posts: 13,584

## Re: calculating amounts by roll up Supplementary acc to basic acc

You need to let use know what data type your variables are. One solution, if I understand your want, would require that the SUB_ACCNO  actually be numeric but you do not indicate if that is so.

That is assuming that when total AMT for these two rows
11111 | 12345 | GOLD | 11111GOLD | B | 50

11111 | 86475 | GOLD | 11111GOLD | S | 130

that you have sub_accno = 12345 in the result as it is the "lower" value.

```proc summary data=have nway;
class acc_no product acc_plus category;
var sub_accno amt;
output out=want(drop= :) min(sub_accno)=
sum(amt)=
;
run;```

However if Sub_accno is character the above won't work and another approach is needed.

Contributor
Posts: 60

## Re: calculating amounts by roll up Supplementary acc to basic acc

Thank ballardw for your time, i actually just want to cumulative the AMT from those account with Category(S) to same Product v Category(B).
Posts: 1,147

## Re: calculating amounts by roll up Supplementary acc to basic acc

``````data have;
infile cards dlm='|';
input ACC_NO SUB_ACCNO PRODUCT\$ ACC_PLUS_PRODUCT :\$20. CATEGORY \$ AMT;
cards;
11111 | 12345 | GOLD | 11111GOLD | B | 50
11111 | 24895 | BLACK | 11111BLACK | B | 25
11111 | 24445 | BLACK | 11111BLACK | B | 75
11111 | 27894 | SILVER | 11111SILVER | B | 140
11111 | 23456 | SILVER | 11111SILVER | S | 70
11111 | 86475 | GOLD | 11111GOLD | S | 130
22222 | 34567 | SILVER | 22222SILVER | B | 100
22222 | 12458 | GOLD | 22222GOLD | B | 50
22222 | 12557 | GOLD | 22222GOLD | S | 100
33333 | 23785 | GOLD | 33333GOLD | B | 75
33333 | 12968 | SILVER | 33333SILVER | B | 110
33333 | 12789 | SILVER | 33333SILVER | S | 110
;

proc sql;
create table want as select *, sum(amt) as sumamt from have
group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT having category='B';
quit; ``````
Thanks,
Jag
Contributor
Posts: 60

## Re: calculating amounts by roll up Supplementary acc to basic acc

thank Jag for your time, maybe my bad english make you misunderstood. let me elaborate more detail. BASIC will always never delete, SUPPLEMENTARY will delete off from listing and it AMT will be roll up to BASIC ACC in AMT.

eg 1:

11111 | 12345 | GOLD | 11111GOLD | B | 50

11111 | 86475 | GOLD | 11111GOLD | S | 130

outcome:

11111 | 12345 | GOLD | 11111GOLD | B | 180

eg 2no change as no Supplementary acc under 11111BLACK)

11111 | 24895 | BLACK | 11111BLACK | B | 25
11111 | 24445 | BLACK | 11111BLACK | B | 75

outcome:

11111 | 24895 | BLACK | 11111BLACK | B | 25
11111 | 24445 | BLACK | 11111BLACK | B | 75

eg3:

11111 | 27894 | SILVER | 11111SILVER | B | 140
11111 | 23456 | SILVER | 11111SILVER | S | 70

outcome:

11111 | 27894 | SILVER | 11111SILVER | B | 210

eg4:

22222 | 34567 | SILVER | 22222SILVER | B | 100

outcome:no change as no Supplementary acc under 22222SILVER)

22222 | 34567 | SILVER | 22222SILVER | B | 100

eg5:

22222 | 12458 | GOLD | 22222GOLD | B | 50
22222 | 12557 | GOLD | 22222GOLD | S | 100

outcome:

22222 | 12458 | GOLD | 22222GOLD | B | 150

and so on....hope above clarify

Posts: 1,147

## Re: calculating amounts by roll up Supplementary acc to basic acc

Thanks for the clarification , please try the below code

``````proc sql;
create table want(drop=ord sumamt amt cnt) as select *, case when cnt=1 then sum(sumamt) else amt end as sumamt2,case when category='B' then 1 else 2 end as ord from (select *, count(*) as cnt,sum(amt) as sumamt from have
group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT,CATEGORY) group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT having min(ord)=ord;
quit;
``````
Thanks,
Jag
Contributor
Posts: 60

## Re: calculating amounts by roll up Supplementary acc to basic acc

[ Edited ]

Thank you so much Jag, the sql code your provide it work, one more problem i encounter, if data change to below, another 2 row data(last 2 row) added in:-

33333 | 54368 | SILVER | 33333SILVER | S | 50
33333 | 94256 | SILVER | 33333SILVER | S | 175

```data have;
infile cards dlm='|';
input ACC_NO SUB_ACCNO PRODUCT\$ ACC_PLUS_PRODUCT :\$20. CATEGORY \$ AMT;
cards;
11111 | 12345 | GOLD | 11111GOLD | B | 50
11111 | 24895 | BLACK | 11111BLACK | B | 25
11111 | 24445 | BLACK | 11111BLACK | B | 75
11111 | 27894 | SILVER | 11111SILVER | B | 140
11111 | 23456 | SILVER | 11111SILVER | S | 70
11111 | 86475 | GOLD | 11111GOLD | S | 130
22222 | 34567 | SILVER | 22222SILVER | B | 100
22222 | 12458 | GOLD | 22222GOLD | B | 50
22222 | 12557 | GOLD | 22222GOLD | S | 100
33333 | 23785 | GOLD | 33333GOLD | B | 75
33333 | 12968 | SILVER | 33333SILVER | B | 110
33333 | 12789 | SILVER | 33333SILVER | S | 110
33333 | 54368 | SILVER | 33333SILVER | S | 5033333 | 94256 | SILVER | 33333SILVER | S | 175
;
proc sql;
create table want(drop=ord sumamt amt cnt) as select *, case when cnt=1 then sum(sumamt) else amt end as sumamt2,case when category='B' then 1 else 2 end as ord from (select *, count(*) as cnt,sum(amt) as sumamt from have
group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT,CATEGORY) group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT having min(ord)=ord;
quit;```

output i get:

11111 24895 BLACK 11111BLACK B 25
11111 24445 BLACK 11111BLACK B 75
11111 12345 GOLD 11111GOLD B 180
11111 27894 SILVER 11111SILVER B 210
22222 12458 GOLD 22222GOLD B 150
22222 34567 SILVER 22222SILVER B 100
33333 23785 GOLD 33333GOLD B 75
33333 12968 SILVER 33333SILVER B 1115

11111 24895 BLACK 11111BLACK B 25
11111 24445 BLACK 11111BLACK B 75
11111 12345 GOLD 11111GOLD B 180
11111 27894 SILVER 11111SILVER B 210
22222 12458 GOLD 22222GOLD B 150
22222 34567 SILVER 22222SILVER B 100
33333 23785 GOLD 33333GOLD B 75
33333 12968 SILVER 33333SILVER B 445

Solution
‎06-23-2017 10:51 AM
Posts: 1,147

## Re: calculating amounts by roll up Supplementary acc to basic acc

[ Edited ]

The below sql code should ovecome the issue, please try and let me know

``````
proc sql;
create table want(drop=amt sort sum) as select a.*, b.sum, case when sum>1 then sum(amt) else amt end as sumamt2,case when category='B' then 1 else 2 end as sort from have as a left join (select distinct ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, sum(ord) as sum from (select distinct ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, category,case when category='B' then 1 else 2 end as ord from have
group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT,category) group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT) as b on a.ACC_NO=b.ACC_NO and a.PRODUCT=b.PRODUCT and a.ACC_PLUS_PRODUCT=b.ACC_PLUS_PRODUCT
group by a.ACC_NO, a.PRODUCT, a.ACC_PLUS_PRODUCT having min(sort)=sort;
quit;
``````

Thanks,
Jag
Contributor
Posts: 60

## Re: calculating amounts by roll up Supplementary acc to basic acc

Jag, this sql code work like charm, really thank you for your advice. However, i'm just new to SAS, didnt know much about SQL, do you mind to share the logical behind this code, what the process to sort this out.

Posts: 1,147

## Re: calculating amounts by roll up Supplementary acc to basic acc

``````step1:
select distinct ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, category,case when category='B' then 1 else 2 end as ord from have
group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT,category

The above code will select the distinct records based on ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, category variables with a new variable
ord where it assigns 1 for category B and 2 for category S

Step2: using the data from step1 code will sum teh ord variable on  ACC_NO, PRODUCT, ACC_PLUS_PRODUCT. By this we get
value 3 for those records which have category B and S and 1 for those records which have category B alone
select distinct ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, sum(ord) as sum

step3:
We merge by left join the above code with sum variable with original dataset so that the original dataset will get the sum variable.
the merging happens by  a.ACC_NO=b.ACC_NO and a.PRODUCT=b.PRODUCT and a.ACC_PLUS_PRODUCT=b.ACC_PLUS_PRODUCT. we again derive a new
sumamt2 variable from amt variable by case when sum>1 then sum(amt) else amt end as sumamt2, where if the sum value is 1 then amt values will be retained
and if sum value is not 1 then the amt values are summed into sumamt2 variable and this happens on group by variable
a.ACC_NO, a.PRODUCT, a.ACC_PLUS_PRODUCT and the having min(sort)=sort is to get the unique records on these records. ``````

Thanks,
Jag
Contributor
Posts: 60