BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BellK
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Reeza
Super User

Try PROC TIMESERIES instead to fill in missing time points.

BellK
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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