topic Re: Output entire row based on IF statement in SAS Programming

Output entire row based on IF statement

Tyler_G — Mon, 20 Mar 2017 04:04:35 GMT

Hey there,

I'm trying to output the previous row of a table based on an IF statement. So what I want is to do output the line above if the criteria of the IF statement is met. Something like the following:

data have;

set want;

IF (YEAR = FYEAR) and (DATADATE>EVENTDATE) THEN OUTPUT;

run; quit;

But of course that is only outputing that row, I want to be able to output the row above in my sorted data set.

Thank you!!!

P.s Version 9.4

Re: Output entire row based on IF statement

LaurieF — Mon, 20 Mar 2017 04:07:33 GMT

The simple answer, really, is that you can't do it on just the example you've shown. However it may be possible.

Can you provide some sample data, and how you want the output to look?

Re: Output entire row based on IF statement

Tyler_G — Mon, 20 Mar 2017 04:20:30 GMT

CUSIP	DATADATE	FYEAR	AT	CEQ	CSHO	PRCC_F	EVENT_DATE	YEAR
88630	31/10/1989	1989	143.831	110.47	13.218	15.375	25/06/2002	2002
88630	31/10/1990	1990	181.665	134.013	13.289	16.375	25/06/2002	2002
88630	31/10/1991	1991	247.169	158.374	13.429	24.375	25/06/2002	2002
88630	31/10/1992	1992	240.762	182.188	13.61	36.25	25/06/2002	2002
88630	31/10/1993	1993	280.054	220.394	27.697	36.5	25/06/2002	2002

So above is roughly what I'm working with.

What is want is to test whether FYEAR = YEAR and if EVENT_DATE < DATADATE (therefore if they are in the same year and the data date is after the event date). If this is the case I want to output the previous years data, which is just the row above after it has been sorted. So output format will be the same. Basically I am trying to cull a data set to only include specific rows and that involves taking the row above if the criteria is met.

Hope that helps, and thank you for the reply.

Re: Output entire row based on IF statement

art297 — Mon, 20 Mar 2017 04:36:15 GMT

Here is one way it can be done:

data class;
  set sashelp.class;
run;

data want (drop=Next_age);
  merge class
        class (keep=age
               rename=(age=Next_age)
               firstobs=2);
  if Next_age eq 13 then output;
run;

Art, CEO, AnalystFinder.com

Re: Output entire row based on IF statement

Shmuel — Mon, 20 Mar 2017 05:21:06 GMT

Can you sort the data in oposit order, than instead output the previos row you need output the next row.

You can initate a flag off. If condition is true turn the flag on.

Geting next observation, if flag is in do the output.

Re: Output entire row based on IF statement

Tyler_G — Mon, 20 Mar 2017 05:45:32 GMT

How would I output the next row then? What code do you recommend?

Re: Output entire row based on IF statement

Shmuel — Mon, 20 Mar 2017 05:50:15 GMT

Use next pseudo code order:

data want;

set have;

retain flag 0;

if flag=1 then output;

if <condition> is true then flag=1;

.... any other code with current row ...

run;

Re: Output entire row based on IF statement

Tyler_G — Mon, 20 Mar 2017 06:30:18 GMT

This only worked for the first set of dates. Then for the rest nothing happened. So it only cut off a few data from the top. Additionally I only want it to output the row previous, not all rows previous. So just the one directly before (or after in descending order).

Re: Output entire row based on IF statement

Kurt_Bremser — Mon, 20 Mar 2017 07:31:07 GMT

You will need to add a statement that resets the flag to zero once a suiting condition is met, if you want to repeat the procedure throughout the dataset.

Re: Output entire row based on IF statement

art297 — Mon, 20 Mar 2017 13:28:39 GMT

My previous example may not have been sufficiently clear or I don't understand what you are trying to do.

I changed one record (#4) in your data so that at least one record would meet the conditions (i.e., record #4). The code outputs record number 3.

data have;
  informat DATADATE EVENT_DATE anydtdte10.;
  format DATADATE EVENT_DATE date9.;
  infile cards dlm=',';
  input CUSIP DATADATE FYEAR AT CEQ CSHO PRCC_F EVENT_DATE YEAR;
  cards;
88630,31/10/1989,1989,143.831,110.47,13.218,15.375,25/06/2002,2002
88630,31/10/1990,1990,181.665,134.013,13.289,16.375,25/06/2002,2002
88630,31/10/1991,1991,247.169,158.374,13.429,24.375,25/06/2002,2002
88630,31/10/1992,1992,240.762,182.188,13.61,36.25,25/06/1992,1992
88630,31/10/1993,1993,280.054,220.394,27.697,36.5,25/06/2002,2002
;

data want (drop=Next_:);
  merge have
        have (keep=year fyear event_date datadate
               rename=(year=Next_year
                       fyear=Next_fyear
                       event_date=next_event_date
                       datadate=next_datadate)
               firstobs=2);
  if Next_FYEAR = Next_YEAR and Next_EVENT_DATE < Next_DATADATE;
run;

Art, CEO, AnalystFinder.com

Re: Output entire row based on IF statement

Shmuel — Mon, 20 Mar 2017 21:08:13 GMT

@Tyler_G wrote: "This only worked for the first set of dates." - and you are right.

@Kurt_Bremser gave the answer.

here is the fixed code:

data want;
  set have;
        retain flag 0;
        if flag=1 then output;
        if <condition> is true then flag=1; else flag=0;  /* <<< line changed */
        .... any other code with current row ...
run;