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

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
;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

6 REPLIES 6
PGStats
Opal | Level 21

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;
PG
stat_sas
Ammonite | Level 13

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;

 

Reeza
Super User
Will the >= signs change or will it always be greater than or equal to? If so, I might recommend a macro in this case, with a data table to drive the macro.

PGStats
Opal | Level 21

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;
PG
Astounding
PROC Star

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;

stat_sas
Ammonite | Level 13

Thanks everyone for the suggestions. This is very helpful and lead me to get a solution of my problem.

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
  • 6 replies
  • 4011 views
  • 0 likes
  • 4 in conversation