BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasphd
Lapis Lazuli | Level 10

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  

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

22 REPLIES 22
Reeza
Super User

@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;

 

sasphd
Lapis Lazuli | Level 10

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

sasphd
Lapis Lazuli | Level 10
I think the year+1 is the problem
Reeza
Super User

@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;

sasphd
Lapis Lazuli | Level 10

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;

 

Kurt_Bremser
Super User

@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.

sasphd
Lapis Lazuli | Level 10

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

Kurt_Bremser
Super User

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


 

Kurt_Bremser
Super User

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.

sasphd
Lapis Lazuli | Level 10

Reeza
Super User

Both solutions don't work? 

Kurt_Bremser
Super User

@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  

sasphd
Lapis Lazuli | Level 10

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):(Column). 

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 

Reeza
Super User

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. 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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