Using Lag statement and post to manipulate a SAS data set

Accepted Solution Solved
Reply
Super Contributor
Posts: 292
Accepted Solution

Using Lag statement and post to manipulate a SAS data set

Hello, I wish to ask if any one can help me with the following code to create data Sim_Change from Sim. The number of times that the column X are equal is not limited to 3, it can be up to 20. I just limit it to 3 for this example.

Data  sim;

Input X Y S,

Cards;

3     5  0

7    6   0

7    8   0

7    9   0

8   10   1

8   11   1

9    13   0

9    14   0

9    15   0

...

...

Run;

I want to change the data to look like this:

Data  sim_Change;

Input X Y  S,

Cards;

3      5   0

7       6   0

7       6  0

7       6   0

8       10  1

8       11  1

9      13  0

9      13   0

9      13   0

Run;

That is when S=0,  X= previous X or X= post X, then Y=First value of Y. When S=1, I want to leave it as it is


Accepted Solutions
Solution
‎01-16-2015 02:54 PM
Frequent Contributor
Posts: 115

Re: Using Lag statement and post to manipulate a SAS data set

Data  sim_Change;

Input X Y  S;

Cards;

3      5   0

7       6   0

7       8  0

7       9   0

8       10  1

8       11  1

9      13  0

9      14   0

9      15   0

Run;

data want;

set sim_change;

by x notsorted;

retain k;

if first.x then k=y;

if not first.x and s=0 then y=k;

drop k;

run;

View solution in original post


All Replies
Grand Advisor
Posts: 17,355

Re: Using Lag statement and post to manipulate a SAS data set

You don't need lag/post (whatever that is).

Use by processing and check if the record is first/last. If it's not both then then that means there's multiples.

Respected Advisor
Posts: 3,775

Re: Using Lag statement and post to manipulate a SAS data set

What said.

Data  sim;
Input X Y;
Cards;
3     5
7    6
7    8
7    9
8   10
9    13
9    14
9    15
;;;;
   run;
data sim2;
   do until(last.x);
      set sim;
      by x;
      if first.x then newy=y;
      output;
     
end;
  
rename newy=y;
   drop y;
   run;
proc print;
  
run;
Contributor
Posts: 53

Re: Using Lag statement and post to manipulate a SAS data set

Your lag statement can be executed inside an IF and there has to be a retain along with it. Something like this,

retain y1;

input x ;

x1 = lag(x);<- Stores value of x from  previous iteration.

if x ne x1 then y1 = y; <- Compares value of current x with previous x(i.e. x1) and assigns value of y to y1.

In the dataset options, drop y and rename y1= y.

Or use the first. and last. as suggested by user above. This is the much clearer method.

retain y1;

If first.x then  y1 = y;

In the dataset options, drop y and rename y1= y.

Super Contributor
Posts: 292

Re: Using Lag statement and post to manipulate a SAS data set

Hello Fareeza Khurshed I really like you code. I added a third variable. I wish to ask if you can help modify your code with this third variable. I am making changes only when S=0 and leave it  untouched when S=1

Contributor
Posts: 53

Re: Using Lag statement and post to manipulate a SAS data set

You replied to me btw -

In response to your question -

Data  sim_Change;

Input X Y  S,

Cards;

3      5   0

7       6   0

7       6  0

7       6   0

8       10  1

8       11  1

9      13  0

9      13   0

9      13   0

Run;

proc sort; by x s;

data data1(drop=y rename=(y1=y));

set sim_change;

by x s;

retain y1;

if s eq 0 then do;

If first.x then y1 = y;

end;

else y1 = y;

This is assuming same x can have different values of s.

Super Contributor
Posts: 292

Re: Using Lag statement and post to manipulate a SAS data set

I do not think this does the job, I want to work from sim and create sim_change as below. From your response, you claim sim_change is known. I want to go from data sim to sim_change directly;

Hello, I wish to ask if any one can help me with the following code to create data Sim_Change from Sim. The number of times that the column X are equal is not limited to 3, it can be up to 20. I just limit it to 3 for this example.

Data  sim;

Input X Y S,

Cards;

3     5  0

7    6   0

7    8   0

7    9   0

8   10   1

8   11   1

9    13   0

9    14   0

9    15   0

...

...

Run;

I want to change the data to look like this:

Data  sim_Change;

Input X Y  S,

Cards;

3      5   0

7       6   0

7       6  0

7       6   0

8       10  1

8       11  1

9      13  0

9      13   0

9      13   0

Run;

That is when S=0,  X= previous X or X= post X, then Y=First value of Y. When S=1, I want to leave it as it is

Contributor
Posts: 53

Re: Using Lag statement and post to manipulate a SAS data set

I copied the wrong set of data, that is all. Did you even try the code?

Super Contributor
Posts: 292

Re: Using Lag statement and post to manipulate a SAS data set

Thank you, it works

Solution
‎01-16-2015 02:54 PM
Frequent Contributor
Posts: 115

Re: Using Lag statement and post to manipulate a SAS data set

Data  sim_Change;

Input X Y  S;

Cards;

3      5   0

7       6   0

7       8  0

7       9   0

8       10  1

8       11  1

9      13  0

9      14   0

9      15   0

Run;

data want;

set sim_change;

by x notsorted;

retain k;

if first.x then k=y;

if not first.x and s=0 then y=k;

drop k;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 389 views
  • 1 like
  • 5 in conversation