topic Re: repeat the last observation in SAS Procedures

repeat the last observation

sasphd — Wed, 07 Jun 2017 13:09:34 GMT

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

Re: repeat the last observation

Reeza — Wed, 07 Jun 2017 13:21:00 GMT

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

Re: repeat the last observation

Kurt_Bremser — Wed, 07 Jun 2017 13:19:11 GMT

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.

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 13:43:28 GMT

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?????

Re: repeat the last observation

Reeza — Wed, 07 Jun 2017 13:45:21 GMT

Both solutions don't work?

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 13:47:16 GMT

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

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 13:48:30 GMT

I think the year+1 is the problem

Re: repeat the last observation

Reeza — Wed, 07 Jun 2017 13:49:01 GMT

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

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 13:54:33 GMT

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;

Re: repeat the last observation

Kurt_Bremser — Wed, 07 Jun 2017 13:57:50 GMT

@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

Re: repeat the last observation

Kurt_Bremser — Wed, 07 Jun 2017 14:04:20 GMT

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

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 14:15:01 GMT

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

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 14:18:12 GMT

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

Re: repeat the last observation

Reeza — Wed, 07 Jun 2017 14:22:07 GMT

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.

Re: repeat the last observation

Kurt_Bremser — Wed, 07 Jun 2017 14:24:16 GMT

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

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 14:39:24 GMT

this is the real data

data test;
input rank_no rankdate eq;
cards;
1 19880331 .
1 19880630 .
1 19880930 .
1 19881231 .
1 19890331 .
1 19890630 .
1 19890930 .
1 19891231 .
1 19900331 .
1 19900630 .
1 19900930 .
1 19901231 39.2
1 19910331 .
1 19910630 .
1 19910930 .
1 19911231 127.336
1 19920331 .
1 19920630 .
1 19920930 .
1 19921231 252.952
1 19930331 .
1 19930630 .
1 19930930 .
1 19931231 209.567
1 19940331 .
1 19940630 .
1 19940930 .
1 19941231 239.622
1 19950331 .
1 19950630 .
1 19950930 .
1 19951231 265.222
1 19960331 .
1 19960630 .
1 19960930 .
1 19961231 458.352
1 19970331 .
1 19970630 .
1 19970930 .
1 19971231 881.127
1 19980331 .
1 19980630 .
1 19980930 .
1 19981231 1752.1
1 19990331 .
1 19990630 .
1 19990930 .
1 19991231 2298.734
1 20000331 .
1 20000630 .
2 19911231 .
2 19920331 .
2 19920630 .
2 19920930 .
2 19921231 .
2 19930331 .
2 19930630 .
2 19930930 .
2 19931231 .
2 19940331 .
2 19940630 .
2 19940930 .
2 19941231 2.792
2 19950331 .
2 19950630 .
2 19950930 .
2 19951231 2.795
2 19960331 .
2 19960630 .
2 19960930 .
2 19961231 8.635
2 19970331 .
2 19970630 .
2 19970930 .
2 19971231 20.477
2 19980331 .
2 19980630 .
2 19980930 .
2 19981231 48.377
2 19990331 .
2 19990630 .
2 19990930 .
2 19991231 56.088
;
run;

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 15:03:55 GMT

I try to change the chrac to numeric but it does not work

data test;
input id rankdate eq;
cards;
1 19880331 .
1 19880630 .
1 19880930 .
1 19881231 .
1 19890331 .
1 19890630 .
1 19890930 .
1 19891231 .
1 19900331 .
1 19900630 .
1 19900930 .
1 19901231 39.2
1 19910331 .
1 19910630 .
1 19910930 .
1 19911231 127.336
1 19920331 .
1 19920630 .
1 19920930 .
1 19921231 252.952
1 19930331 .
1 19930630 .
1 19930930 .
1 19931231 209.567
1 19940331 .
1 19940630 .
1 19940930 .
1 19941231 239.622
1 19950331 .
1 19950630 .
1 19950930 .
1 19951231 265.222
1 19960331 .
1 19960630 .
1 19960930 .
1 19961231 458.352
1 19970331 .
1 19970630 .
1 19970930 .
1 19971231 881.127
1 19980331 .
1 19980630 .
1 19980930 .
1 19981231 1752.1
1 19990331 .
1 19990630 .
1 19990930 .
1 19991231 2298.734
1 20000331 .
1 20000630 .
2 19911231 .
2 19920331 .
2 19920630 .
2 19920930 .
2 19921231 .
2 19930331 .
2 19930630 .
2 19930930 .
2 19931231 .
2 19940331 .
2 19940630 .
2 19940930 .
2 19941231 2.792
2 19950331 .
2 19950630 .
2 19950930 .
2 19951231 2.795
2 19960331 .
2 19960630 .
2 19960930 .
2 19961231 8.635
2 19970331 .
2 19970630 .
2 19970930 .
2 19971231 20.477
2 19980331 .
2 19980630 .
2 19980930 .
2 19981231 48.377
2 19990331 .
2 19990630 .
2 19990930 .
2 19991231 56.088
;
run;
data want ;
set test ;
array _char eq ;
array _num 8 eq1;
do i=1 to dim(_char);
_num(i) = input(_char(i),best32.);
end;
drop eq1 i;
run;
data want1;
set want;
by ID;
retain lstv;
If first.ID then lstv = "";
if EQ ne "" then lstv=EQ;
if EQ="" then EQ=lstv;
run;

Re: repeat the last observation

Reeza — Wed, 07 Jun 2017 15:13:20 GMT

Numeric missing is a period.

Character missing is a 'blank' or 'space'.

Look at how my code checks for missing versus what youre using.

I'm 99% sure my code is correct given your new data.

Re: repeat the last observation

Reeza — Wed, 07 Jun 2017 15:14:17 GMT

Or switch to the MISSING function, which handles either.

If missing(eq) then ...

Re: repeat the last observation

sasphd — Wed, 07 Jun 2017 15:29:57 GMT

How can I change year+1 in your program

Data want1;
Set test;

By Id; retain new_EQ;
if missing(eq) then new_eq = eq;

else if last.id then Do; next(rankdate); output;
Output;
run;