Value of variable depending on one entry of the data table

[LeoBrunner]

Dear SAS-Community

I'm failing with a problem that actually sounds not very complicated:

I have a data table of the form as I've attached. For the case that you cannot open it, it looks like this:

Code    year     value

10        2015    375

10        2016    640

10        2017    710

20        2014        0

20        2015    125

20        2016    950

20        2017        0

What I like to do is the following: If the value of the line with code = 20 and year = 2017 is zero (as in my example), then I like to set the values of code 20 (only code 20!) for all years to zero. In my example this would mean that instead of the values 125 and 950 there should be written zero.

I'm very grateful for every help!

Best regards, Leo

[Reeza · Accepted solution]

Since 2017 is the largest value sort it descending. 

Then 2017 will be the first value.

proc sort data=have;
by code descending year;
run;

data want;
set have;
retain flag;

if first.code and year=2017 and value=0 then flag=1;
else if first.code and year=2017 and value ne  0 then flag=0;

if flag=1 then value=0;

run;

[LeoBrunner]

Thank you very much, Reeza!

Best regards, Leo

[novinosrin]

data have;
input Code    year     value ;
cards;
10        2015    375
10        2016    640
10        2017    710
20        2014        0
20        2015    125
20        2016    950
20        2017        0
;

proc sql;
create table want as
select code,year ,abs(max(value = 0 and year=2017)-1)*value as value
from have
group by code
order code,year;
quit;

[LeoBrunner]

Thank you very much novinosrin! Best regards, Leo

[Astounding]

To me, this would be the most intuitive approach:

data want;

if _n_=1 then do;

   set have (where=(year=2017 and code=20));

   if value=0 then reset=1;

   retain reset;

end;

set have;

if code=20 and reset=1 then value=0;

drop reset;

run;

This assumes that you always have one observation where YEAR is 2017 and CODE is 20.

[LeoBrunner]

Thank you very much, Astounding! Best regards, Leo

[mahesh146]

Please check below code.

DATA TEMP;
INPUT Code year value;
CARDS;
10 2015 375
10 2016 640
10 2017 710
20 2014 0
20 2015 125
20 2016 950
20 2017 0
;
RUN;


PROC PRINT DATA=TEMP;
TITLE 'Source Table';
RUN;

PROC SQL;
CREATE TABLE TEMP1 AS
SELECT
Code,
Year,
MIN(value) as Min_Val,
CASE
WHEN calculated Min_Val=0 THEN 0
ELSE value
END as Final_Value
FROM
TEMP
GROUP BY
Code
;
QUIT;

DATA TEMP;
SET TEMP1;
RENAME Final_value=Value;
DROP Min_Val;
RUN;

PROC PRINT DATA=TEMP;
TITLE 'Final Table';
RUN;

[LeoBrunner]

Thank you very much, mahesh! Best regards, Leo

[Ksharp]

data have;
input Code    year     value ;
cards;
10        2015    375
10        2016    640
10        2017    710
20        2014        0
20        2015    125
20        2016    950
20        2017        0
;

data want;
 do until(last.code);
   set have;
   by code;
   if year=2017 and value=0 then yes=1;
 end;
 do until(last.code);
   set have;
   by code;
   if yes then value=0;
   output;
 end;
 drop yes;
 run;

[LeoBrunner]

Thank you very much, Ksharp! Best regards, Leo