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;
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;
Try PROC TIMESERIES instead to fill in missing time points.
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.
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;
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 🙂
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.