Help using Base SAS procedures

repeat the last observation

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 118
Accepted Solution

repeat the last observation

I want to repeat the last observation for every ID

 

I have this table A

ID    year      EQ       

1      1899       .

1      1900      5           

1      1901      .           

1      1902      .          

2      2000      .        

2      2001      4          

2      2002       .       

2      2003       5         

I want this table 

ID    year      EQ       

1      1899       .

1      1900      5           

1      1901      5           

1      1902      5          

2      2000      .        

2      2001      4          

2      2002       4       

2      2003       5  

 

I write this program

data joint1;
set joint;
retain lstv;
if EQ ne "" then lstv=EQ;
if EQ="" then EQ=lstv;

run;

however this program repeat the observation and does not care about id. it give me this. the 5 here is wrong it takes the observation of a bad id

ID    year      EQ       

1      1899       .

1      1900      5           

1      1901      5           

1      1902      5          

2      2000      5        

2      2001      4          

2      2002       4       

2      2003       5  


Accepted Solutions
Solution
‎06-08-2017 10:08 AM
Super User
Posts: 17,868

Re: repeat the last observation

We answered your question. 

 

My initial answer minus the DO/OUTPUT sections because you're not actually repeating any observations. You're only doing Last Obs Carried Forward. You can search that term with SAS if you want further solutions. 

 

Data want;
Set have;

By Id;

Retain new_eq;

If first.id then new_eq = eq;
If not missing(eq) then new_eq = eq;

run;

View solution in original post


All Replies
Super User
Posts: 17,868

Re: repeat the last observation

[ Edited ]

sasphd wrote:

I want to repeat the last observation for every ID

 

I have this table A

ID    year      EQ       

1      1899       .

1      1900      5           

1      1901      .           

1      1902      .          

2      2000      .        

2      2001      4          

2      2002       .       

2      2003       5         

I want this table 

ID    year      EQ       

1      1899       .

1      1900      5           

1      1901      5           

1      1902      5          

2      2000      .        

2      2001      4          

2      2002       4       

2      2003       5  

 

I write this program

data joint1;
set joint;
retain lstv;
if EQ ne "" then lstv=EQ;
if EQ="" then EQ=lstv;

run;

however this program repeat the observation and does not care about id. it give me this. the 5 here is wrong it takes the observation of a bad id

ID    year      EQ       

1      1899       .

1      1900      5           

1      1901      5           

1      1902      5          

2      2000      5        

2      2001      4          

2      2002       4       

2      2003       5  


Your question doesn't match your sample. You're looking to:

1. Fill missing values 

2. Add a new record at end of each ID (not repeat) with year incremented. 

 

BY GROUPS + OUTPUT statement. 

 

Edit: This assumes your output shown is what you want not what you actually stated. 

 

Data want;
Set have;

By Id; retain new_EQ;

if eq ne . The new_eq = eq; If last.id then Do; year+1; output; Output; Run;

 

Frequent Contributor
Posts: 118

Re: repeat the last observation

REEZA your solution does not work because in my real data I have monthly data of this form 19880331

Frequent Contributor
Posts: 118

Re: repeat the last observation

I think the year+1 is the problem
Super User
Posts: 17,868

Re: repeat the last observation


sasphd wrote:

REEZA your solution does not work because in my real data I have monthly data of this form 19880331


Yeah, I CANT SEE YOUR DATA. All I see is what you post. And like I mentioned it's inconsistent. This will replicate your output as posted. 

 

Try this:

 

Data want;
Set have;

By Id; retain new_EQ;

if eq ne . The new_eq = eq;

 

Run;

Frequent Contributor
Posts: 118

Re: repeat the last observation

I try this but not change in my data 

data joint1;
set joint;
by rank_no;

retain lstv;
if EQ ne "" then lstv=EQ;
run;

 

Super User
Posts: 6,951

Re: repeat the last observation


sasphd wrote:

REEZA your solution does not work because in my real data I have monthly data of this form 19880331


If you want a solution for your data, THEN POST YOUR DATA AS IS AND DON'T LET US GUESS!!!

 

I gave you an example how to post data in a data step. Please do so.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 118

Re: repeat the last observation

I never succeed to put the data at the right format for the forum

Super User
Posts: 6,951

Re: repeat the last observation

Copy and expand my data step that I already gave you. This is basic SAS technique, without which you won't ever be any good with SAS. Really.


sasphd wrote:

I never succeed to put the data at the right format for the forum


 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,951

Re: repeat the last observation

Use the power of by processing:

data joint1;
set joint;
by ID;
retain lstv;
If first.ID then lstv = "";
if EQ ne "" then lstv=EQ;
if EQ="" then EQ=lstv;
run;

Correct sorting of dataset joint is required, of course.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 118

Re: repeat the last observation

Super User
Posts: 17,868

Re: repeat the last observation

Both solutions don't work? 

Super User
Posts: 6,951

Re: repeat the last observation

[ Edited ]

sasphd wrote:


Look again.

 

data joint;
input id year eq;
cards;
1 1899 .
1 1900 5
1 1901 .
1 1902 .
2 2000 .
2 2001 4
2 2002 .
2 2003 5
;
run;

data joint1;
set joint;
by ID;
retain lstv;
If first.ID then lstv = "";
if EQ ne "" then lstv=EQ;
if EQ="" then EQ=lstv;
run;

proc print data=joint1 noobs;
run;

Result:

id    year    eq    lstv

 1    1899     .        
 1    1900     5     5  
 1    1901     5     5  
 1    1902     5     5  
 2    2000     .        
 2    2001     4     4  
 2    2002     4     4  
 2    2003     5     5  

Matches exactly with your intended result as copied from the OP:

ID    year      EQ       

1      1899       .

1      1900      5           

1      1901      5           

1      1902      5          

2      2000      .        

2      2001      4          

2      2002       4       

2      2003       5  

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 118

Re: repeat the last observation

thanks a lot I remark that the problem is on my original data 

when I put only this program log indicates  Character values have been converted to numeric values at the places given by: (Line)Smiley SadColumn). 

data joint1;
set joint;
by ID;
retain lstv;
if EQ ne "" then lstv=EQ;
if EQ="" then EQ=lstv;
run;

 when I add 

If first.ID then lstv = "";

the program does not work 

Super User
Posts: 17,868

Re: repeat the last observation

You'd be better off spending some time learning how to create sample data and how BY groups works. I know SAS is only a tool you're using to do your work but programming is pretty much a requirement for many jobs these days. 

☑ This topic is SOLVED.

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

Discussion stats
  • 22 replies
  • 230 views
  • 1 like
  • 3 in conversation