SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Fill down/expand observations with an observation number

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Fill down/expand observations with an observation number

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;

 


Accepted Solutions
Solution
‎09-07-2017 11:21 AM
Super User
Posts: 5,724

Re: Fill down/expand observations with an observation number

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;

View solution in original post


All Replies
Super User
Posts: 20,731

Re: Fill down/expand observations with an observation number

Try PROC TIMESERIES instead to fill in missing time points.

New Contributor
Posts: 3

Re: Fill down/expand observations with an observation number

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.

Solution
‎09-07-2017 11:21 AM
Super User
Posts: 5,724

Re: Fill down/expand observations with an observation number

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;

New Contributor
Posts: 3

Re: Fill down/expand observations with an observation number

Posted in reply to Astounding

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 Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 318 views
  • 0 likes
  • 3 in conversation