SAS Programming

DATA Step, Macro, Functions and more
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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 4660 views
  • 0 likes
  • 4 in conversation