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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

11 REPLIES 11
sagulolo
Quartz | Level 8

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;
ballardw
Super User

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.

 

sagulolo
Quartz | Level 8
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).
Jagadishkatam
Amethyst | Level 16

 

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
sagulolo
Quartz | Level 8

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 2:(no 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

Jagadishkatam
Amethyst | Level 16

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
sagulolo
Quartz | Level 8

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

 

 

 

Jagadishkatam
Amethyst | Level 16

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
sagulolo
Quartz | Level 8

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.

Jagadishkatam
Amethyst | Level 16

 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
sagulolo
Quartz | Level 8
look like i need to put more time and effort on proc sql.

thank you so much, Jag

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 1303 views
  • 3 likes
  • 3 in conversation