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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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