DATA Step, Macro, Functions and more

Value of variable depending on one entry of the data table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Value of variable depending on one entry of the data table

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


Accepted Solutions
Solution
‎06-05-2018 10:20 AM
Super User
Posts: 23,776

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

Posted in reply to LeoBrunner

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;

View solution in original post


All Replies
Solution
‎06-05-2018 10:20 AM
Super User
Posts: 23,776

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

Posted in reply to LeoBrunner

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;
Occasional Contributor
Posts: 6

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

Thank you very much, Reeza!

Best regards, Leo

PROC Star
Posts: 1,845

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

Posted in reply to LeoBrunner
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;
Occasional Contributor
Posts: 6

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

Posted in reply to novinosrin
Thank you very much novinosrin! Best regards, Leo
Super User
Posts: 6,785

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

Posted in reply to LeoBrunner

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.

Occasional Contributor
Posts: 6

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

Posted in reply to Astounding
Thank you very much, Astounding! Best regards, Leo
Occasional Contributor
Posts: 15

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

Posted in reply to LeoBrunner
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;

Occasional Contributor
Posts: 6

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

Posted in reply to mahesh146
Thank you very much, mahesh! Best regards, Leo
Super User
Posts: 10,787

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

Posted in reply to LeoBrunner
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;
Occasional Contributor
Posts: 6

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

Thank you very much, Ksharp! Best regards, Leo
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 167 views
  • 2 likes
  • 6 in conversation