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
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;
@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;
REEZA your solution does not work because in my real data I have monthly data of this form 19880331
@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;
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;
@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.
I never succeed to put the data at the right format for the forum
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
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.
KurtBremser your solution does not work when first.ID observation is different from "."
Reeza your solution also does not work there is not a error message on the log but your program seems not changing my input?????
Both solutions don't work?
@sasphd wrote:
KurtBremser your solution does not work when first.ID observation is different from "."
Reeza your solution also does not work there is not a error message on the log but your program seems not changing my input?????
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
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
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.