- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I am looking for some iterative process that allows me to get conditional sum based on certain cut off points. For example:
if qty>=1 and sales>=10 then get sum sales
if qty>=2 and sales>=15 then get sum sales
Any suggestions regarding this please?
Please see below a sample data set.
data have;
input sales qty;
datalines;
12 2
24 5
23 3
16 6
15 1
22 6
11 3
;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The data table driven approach would involve a table listing the summing conditions and a translation of the table into a data step. Something like this:
data have;
input sales qty;
datalines;
12 2
24 5
23 3
16 6
15 1
22 6
11 3
;
data conditions;
length condition $64;
input condNo condition &;
datalines;
1 qty>=1 and sales>=10
2 qty>=2 and sales>=15
3 qty>10
;
data _null_;
length var $32 line $200;
set conditions end=done;
if _n_=1 then
call execute("data wantTable; set have end=done;");
var = cats("sumSales_", condNo);
line = catx(" ", "if", condition, "then", var, "+ sales;");
call execute(line);
if done then
call execute("if done then output; keep sumSales_: ; run;");
run;
proc transpose data=wantTable out=wantList; run;
proc sql;
create table want as
select a.condNo, a.condition, b.col1 as sumSales
from conditions as a inner join wantList as b
on a.condNo = input(scan(b._name_, 2, "_"), best.);
select * from want;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Had to guess what you want exactly. Something like this?
data have;
input sales qty;
datalines;
12 2
24 5
23 3
16 6
15 1
22 6
11 3
;
data want;
set have end=done;
if qty>=1 and sales>=10 or
qty>=2 and sales>=15
then sumSales + sales;
if done then output;
keep sumSales;
run;
proc print data=want noobs; run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks PG for the input- I am trying to get sum of sales variable at various cut off points. Something like this
qty sales sum
>=1 >=10 123
>=2 >=15 100
I've tried in the following way:
data want;
set have;
do i=1 to 7 by 1;
do j=10 to 25 by 5;
if qty>=i and sales>=j then do;
tot+sales;
leave;
end;
end;
end;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The data table driven approach would involve a table listing the summing conditions and a translation of the table into a data step. Something like this:
data have;
input sales qty;
datalines;
12 2
24 5
23 3
16 6
15 1
22 6
11 3
;
data conditions;
length condition $64;
input condNo condition &;
datalines;
1 qty>=1 and sales>=10
2 qty>=2 and sales>=15
3 qty>10
;
data _null_;
length var $32 line $200;
set conditions end=done;
if _n_=1 then
call execute("data wantTable; set have end=done;");
var = cats("sumSales_", condNo);
line = catx(" ", "if", condition, "then", var, "+ sales;");
call execute(line);
if done then
call execute("if done then output; keep sumSales_: ; run;");
run;
proc transpose data=wantTable out=wantList; run;
proc sql;
create table want as
select a.condNo, a.condition, b.col1 as sumSales
from conditions as a inner join wantList as b
on a.condNo = input(scan(b._name_, 2, "_"), best.);
select * from want;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is your data set so large that you have to process it only once? For a small data set this should be easy:
proc means data=have sum;
var sales;
where qty >= 1 and sales >= 10;
title 'Results for qty >= 1 and sales >= 10';
run;
proc means data=have sum;
var sales;
where qty >= 2 and sales >= 15;
title 'Results for qty >= 2 and sales >= 15';
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks everyone for the suggestions. This is very helpful and lead me to get a solution of my problem.