BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LeoBrunner
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

10 REPLIES 10
Reeza
Super User

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
Calcite | Level 5

Thank you very much, Reeza!

Best regards, Leo

novinosrin
Tourmaline | Level 20
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
Calcite | Level 5
Thank you very much novinosrin! Best regards, Leo
Astounding
PROC Star

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
Calcite | Level 5
Thank you very much, Astounding! Best regards, Leo
mahesh146
Obsidian | Level 7
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
Calcite | Level 5
Thank you very much, mahesh! Best regards, Leo
Ksharp
Super User
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
Calcite | Level 5
Thank you very much, Ksharp! Best regards, Leo

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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