DATA Step, Macro, Functions and more

calculating amounts by roll up Supplementary acc to basic acc

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

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

 

thank in advance.

 

Best Regards

 


Accepted Solutions
Solution
‎06-23-2017 10:51 AM
Trusted Advisor
Posts: 1,137

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

View solution in original post


All Replies
Contributor
Posts: 49

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: 11,343

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: 49

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).
Trusted Advisor
Posts: 1,137

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: 49

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

Posted in reply to Jagadishkatam

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 2Smiley Sadno 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

Trusted Advisor
Posts: 1,137

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: 49

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

[ Edited ]
Posted in reply to Jagadishkatam

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 | 50
33333 | 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

 

instead of expected outcome:

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

 

Thank in advance

 

 

 

Solution
‎06-23-2017 10:51 AM
Trusted Advisor
Posts: 1,137

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: 49

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

Posted in reply to Jagadishkatam

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.

Trusted Advisor
Posts: 1,137

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

 Hope the below information will help you to understand the code

 

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: 49

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

Posted in reply to Jagadishkatam
look like i need to put more time and effort on proc sql.

thank you so much, Jag
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 166 views
  • 3 likes
  • 3 in conversation