topic Re: Value of variable depending on one entry of the data table in SAS Programming

Value of variable depending on one entry of the data table

LeoBrunner — Sun, 03 Jun 2018 20:04:37 GMT

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

Re: Value of variable depending on one entry of the data table

Reeza — Sun, 03 Jun 2018 20:10:33 GMT

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;

Re: Value of variable depending on one entry of the data table

novinosrin — Sun, 03 Jun 2018 22:02:57 GMT

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;

Re: Value of variable depending on one entry of the data table

Astounding — Sun, 03 Jun 2018 22:55:44 GMT

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.

Re: Value of variable depending on one entry of the data table

mahesh146 — Mon, 04 Jun 2018 06:11:19 GMT

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;

Re: Value of variable depending on one entry of the data table

Ksharp — Mon, 04 Jun 2018 13:04:00 GMT

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;

Re: Value of variable depending on one entry of the data table

LeoBrunner — Tue, 05 Jun 2018 14:20:57 GMT

Thank you very much, Reeza!

Best regards, Leo

Re: Value of variable depending on one entry of the data table

LeoBrunner — Tue, 05 Jun 2018 14:21:44 GMT

Thank you very much novinosrin! Best regards, Leo

Re: Value of variable depending on one entry of the data table

LeoBrunner — Tue, 05 Jun 2018 14:22:28 GMT

Thank you very much, Astounding! Best regards, Leo

Re: Value of variable depending on one entry of the data table

LeoBrunner — Tue, 05 Jun 2018 14:23:00 GMT

Thank you very much, mahesh! Best regards, Leo

Re: Value of variable depending on one entry of the data table

LeoBrunner — Tue, 05 Jun 2018 14:23:19 GMT

Thank you very much, Ksharp! Best regards, Leo