How to interpolate groups of not sorted data

Reply
New Contributor
Posts: 4

How to interpolate groups of not sorted data

Dear all,

My dataset is sorted by id and date. The variable I want to interpolate linearly is not sorted. Can somebody help me to interpolate data that is not ordered?

 

data a;

input id date x;

datalines;

1 1 10

1 4 .

1 8 5

1 10 20

2 1 8

2 2 .

2 5 .

2 5 .

2 10 4

;

run;

Thank you!

Super User
Posts: 19,769

Re: How to interpolate groups of not sorted data

How do you want to interpolate it? What method? Are there any rules? If the values are random, how can you impute it with any certainty?
Respected Advisor
Posts: 4,919

Re: How to interpolate groups of not sorted data

If you have access to SAS/ETS, it is easy to do with proc expand. The only detail to take care of is the duplicate time values as present in your example. I propose to do it with a data view. Here is what you could do

 

data aa / view=aa;
set a; by id date;
if first.date;
run;

proc expand data=aa out=b method=join plots=none;
by id;
id date;
convert x;
run;
PG
Trusted Advisor
Posts: 1,117

Re: How to interpolate groups of not sorted data

If you don't have access to SAS/ETS (like myself), there are several other options, including SAS/IML, PROC TRANSREG (requires SAS/STAT) and pure data step solutions. Please see the following resources:

 

New Contributor
Posts: 4

Re: How to interpolate groups of not sorted data

Hi,

Unfortunately I do not have SAS/ETS. But thanks for giving me some ideas. That’s how I did it:

data aaa;

set a; by id date;

if first.date;

run;

 

proc expand data=aaa out=b method=join plots=none;

by id;

id date;

run;

 

data b;

set b;

x2=x;

drop x;

run;

 

data merge_a_b;

merge a b;

by id date;

run;

 

Respected Advisor
Posts: 4,919

Re: How to interpolate groups of not sorted data

You could have done:

 


data aa / view=aa;
set a; by id date;
if first.date;
run;

proc expand data=aa out=b method=join plots=none;
by id;
id date;
convert x=x2;
run;
PG
Super User
Posts: 19,769

Re: How to interpolate groups of not sorted data

Proc expand is part of SAS/ETS, if you can use it you have SAS/ETS.
Ask a Question
Discussion stats
  • 6 replies
  • 386 views
  • 2 likes
  • 4 in conversation