topic Re: Conditional values in a column in SAS Programming

Conditional values in a column

Ira — Thu, 01 Oct 2015 14:32:48 GMT

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.

Re: Conditional values in a column

ballardw — Thu, 01 Oct 2015 15:14:08 GMT

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.

Re: Conditional values in a column

Steelers_In_DC — Thu, 01 Oct 2015 15:18:20 GMT

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;

Re: Conditional values in a column

Steelers_In_DC — Thu, 01 Oct 2015 15:31:51 GMT

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;