DATA Step, Macro, Functions and more

Conditional values in a column

Reply
Regular Learner Ira
Regular Learner
Posts: 1

Conditional values in a column

If sales on 30-Sep is greater than MTD_Sales then Color should be green for all the dates of a product. Else the color should be red for all the dates of that product.

 

 

 

Super User
Posts: 11,343

Re: Conditional values in a column

What destination are you sending you data to? HTML, RTF, PDF or something else?

What procedure are you attempting to use?

 

Different procedures and destinations likely require different approaches.

Valued Guide
Posts: 860

Re: Conditional values in a column

If you are looking for the code and not the report here you go:

 

data have;
infile cards;
input product$ date mtd_sales goal;
informat date date9.;
format date date9.;
cards;
Handbags 21-Sep-15 23 50
Handbags 22-Sep-15 25 50
Handbags 23-Sep-15 28 50
Handbags 24-Sep-15 29 50
Handbags 25-Sep-15 33 50
Handbags 26-Sep-15 34 50
Handbags 27-Sep-15 37 50
Handbags 28-Sep-15 40 50
Handbags 29-Sep-15 44 50
Handbags 30-Sep-15 45 50
Shoes    21-Sep-15 38 70
Shoes    22-Sep-15 44 70
Shoes    23-Sep-15 46 70
Shoes    24-Sep-15 47 70
Shoes    25-Sep-15 49 70
Shoes    26-Sep-15 57 70
Shoes    27-Sep-15 63 70
Shoes    28-Sep-15 68 70
Shoes    29-Sep-15 72 70
Shoes    30-Sep-15 74 70
;

proc sql;
create table want as
select product,date,mtd_sales,goal,max(color) as color
from (
select *,
case
when date = '30sep2015'd and mtd_sales > goal then 'GREEN'
when date = '30sep2015'd and mtd_sales < goal then 'RED'
end as Color
from have)
group by product;

Valued Guide
Posts: 860

Re: Conditional values in a column

Posted in reply to Steelers_In_DC
or this:
data want(drop=flag);
do until(last.product);
set have;
by product;
if date = '30sep2015'd and mtd_sales > goal then flag = 1;
end;
do until(last.product);
set have;
by product;
if flag = 1 then Color = 'GREEN';
else color = 'RED';
output;
end;
run;
Ask a Question
Discussion stats
  • 3 replies
  • 285 views
  • 0 likes
  • 3 in conversation