Fill down/expand observations with an observation number

[BellK]

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;

[Reeza]

Try PROC TIMESERIES instead to fill in missing time points.

[BellK]

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.

[Astounding · Accepted solution]

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;

[BellK]

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 🙂