topic Re: Fill down/expand observations with an observation number in SAS Data Management

Fill down/expand observations with an observation number

BellK — Thu, 07 Sep 2017 14:23:21 GMT

Hi!

Below is what I have an what I'd like to have, basically I want my data to be balanced with missing values on the variables of interest, but the visit variable expanded. I have been playing around with proc expand, but can't quite work out how to get here as my ID variable (visit) is not a date.

Thank you!

Data have;
 input id visit sum;
 datalines;
1 1 20
1 2 22
1 3 22
2 1 19
2 2 21
2 3 23
2 4 19
3 1 18
;
run;

data want;
input id visit sum;
 datalines;
1 1 20
1 2 22
1 3 22
1 4 .
2 1 19
2 2 21
2 3 23
2 4 19
3 1 18
3 2 .
3 3 .
3 4 .
;
run;

Re: Fill down/expand observations with an observation number

Reeza — Thu, 07 Sep 2017 14:42:14 GMT

Try PROC TIMESERIES instead to fill in missing time points.

Re: Fill down/expand observations with an observation number

Astounding — Thu, 07 Sep 2017 14:55:45 GMT

Base SAS can accomlpish this:

proc sql noprint;

create table visit_list as select distinct visit from have;

create table id_list as select distinct id from have;

create table shell as select * from id_list, visit_list orderby id visit;

quit;

Assuming your original data is already sorted BY ID VISIT:

data want;

merge have shell;

by id visit;

run;

Re: Fill down/expand observations with an observation number

BellK — Thu, 07 Sep 2017 15:20:59 GMT

Thank you, this works with a minor tweek in the line

create table shell as select * from id_list, visit_list orderby id visit;

create table shell as select * from id_list, visit_list order by id, visit;

Much appreciate this flexible solution 🙂

Re: Fill down/expand observations with an observation number

BellK — Thu, 07 Sep 2017 15:22:13 GMT

Thank you, I had missed this once I stumbled upon proc expand

The SQL solution works for now, but this procedure will be handy to learn more about.