DATA Step, Macro, Functions and more

Keep first and last row

Accepted Solution Solved
Reply
Contributor zz
Contributor
Posts: 28
Accepted Solution

Keep first and last row

Hello,

 

I have the following row of data:

Sub   Date                Phase

01     2016-06-06      Before

01     2016-06-24      During

01     2016-07-01      During

01     2016-08-19      During

01     2016-08-26      After

 

I need to keep the first and last During to have the data look like this:

Sub   Date                Phase

01     2016-06-06      Before

01     2016-06-24      During

01     2016-08-19      During

01     2016-08-26      After

 

I tried to use the first. and last variable like below:

 

DATA JR.SV_021;
SET SV_02;
BY PHASE;
IF first.PHASE THEN OUTPUT;
IF last.PHASE THEN OUTPUT;
run;

 

but it does not work for me.  I appreciate your help very much in advance!

 

 

 


Accepted Solutions
Solution
‎09-23-2016 11:24 AM
Super User
Posts: 10,483

Re: Keep first and last row

Describe how "doesn't work": Error, unexpected output, no output.

And show the code ran and the log.

View solution in original post


All Replies
Super User
Posts: 5,079

Re: Keep first and last row

A small change should get this to work.  Change the BY statement to:

 

BY PHASE NOTSORTED;

 

That will permit your BY statement even though the data are not in order by Phase.

 

Also note, if your actual data set is larger and might contain more than one SUB value, you may need to use:

 

BY SUB PHASE NOTSORTED;

 

Either way, the rest of the program can remain the same.

Contributor zz
Contributor
Posts: 28

Re: Keep first and last row

Tried, but somehow, it did not workSmiley Sad

Solution
‎09-23-2016 11:24 AM
Super User
Posts: 10,483

Re: Keep first and last row

Describe how "doesn't work": Error, unexpected output, no output.

And show the code ran and the log.

Contributor zz
Contributor
Posts: 28

Re: Keep first and last row

Hi ballardw, It worked!  I checked and one libref was not correct for input data.  Once that's corrected, it all worked.  Thank you very much!

Contributor zz
Contributor
Posts: 28

Re: Keep first and last row

Hi ballardw,

 

One problem, rows are duplicated, if phase changed in the next row, eg: the first row was duplicated.

Super User
Posts: 10,483

Re: Keep first and last row

Replace

IF last.PHASE THEN OUTPUT;

with

IF last.PHASE and not (First.Phase) THEN OUTPUT

New Contributor
Posts: 3

Re: Keep first and last row

data durga;

input Sub 2. Date:yymmdd10. Phase $ 24-30;

infile datalines;

cards;

01 2016-06-06 Before

01 2016-06-24 During

01 2016-07-01 During

01 2016-08-19 During

01 2016-08-26 After

;

run;

proc sort data=durga out=class;

by Phase;

run;

data durga_;

set class;

by phase;

if first.Phase=1 or last.phase=1;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 406 views
  • 1 like
  • 4 in conversation