How can I dynamically reference a variable in calculations?

[bonc826]

I am working with patient visits data. Each row is a different visit. A patient can have any number of visits, and visits are still ongoing.

data pt_visits;
INPUT patient $ visit_date :MMDDYY10.;
FORMAT visit_date MMDDYY10.;
datalines;
A 01/12/2026
A 01/16/2026
A 02/04/2026
B 01/05/2026
B 01/09/2026
B 01/10/2026
B 01/18/2026
B 01/31/2026
B 02/05/2026
;
run;

Many of the metrics I'm reporting are at the patient level, so I've transposed the data:

proc sort data=pt_visits; by patient visit_date; run;

/*count patient visits*/
data pt_visits_ct;
	set pt_visits;
	by patient;
	if first.patient then visit_ct=0;
	visit_ct+1;
run;

/*one row per patient*/
proc transpose data=pt_visits_ct out=pt_visits_wide prefix=visit;
	by patient;
	id visit_ct;
	var visit_date;
run;

I need to calculate things such as most recent visit and the time that has elapsed since the most recent visit:

data calc;
	set pt_visits_wide;
	last_visit=max(of visit1--visit6);
	days_since_last_visit=today()-max(of visit1--visit6);
	format last_visit mmddyy10.;
run;

My code above uses visit6 in calculations because Patient B, with the most visits, has 6 visits. However, as time goes on and patients have more visits, visit6 will go on to become visit7, then visit8, etc. How can I reference visitn, with n being the number of visits that the patient with the most visits has?

[ballardw]

Your original data structure is probably best for determining "most recent" visit. Sort the data by patient id and descending visit date. The first visit will then be the most recent.

Intervals as well using the Intck and Retain functions.

Proc sort data=Pt_visits out=sorted_visits;
   by patient descending visit_date;
run;

data visit_interval;
   set sorted_visits;
   by patient;
   retain most_recent;
   format most_recent mmddyy10.;
   if first.patient then do;
      most_recent=visit_date;
      date_interval=0;
   end;
   else  date_interval = intck('day',visit_date,most_recent);
   /* drop most_recent;*/
run;

RETAIN keeps the value of the specified variable(s) across the data step boundary until reset. In this case using BY patient allows us to use the FIRST.<by variable name> construct to identify the first patient record and set the most_recent visit date from the sorted values.

INTCK yields interval counts between two date variables. The order of the the two dates is important. Please read the documentation. Date and/or time intervals are usually best worked with the functions INTCK, for returning interval between, and INTNX to increment dates. You can get intervals such as week, month, quarter or year (and others) for example as needed and let the staff at SAS do the background programming.

Unless there is something else needed then the VisitN variable isn't needed though could be build in the step that uses the sorted data.

Note that if multiple FACILITIES are to be used then you may want to sort by patient and facility and use similar logic using First.Facility to hand the most recent and interval per patient facility combination if needed. Or a treatment code, doctor or similar seen by the patient, etc. 

Uncomment the Drop statement if the most_recent visit variable is not needed later.

Often making "wide" data such as your transposes is not needed.

If you are careful with your variable names you can use  MAX (of varname: ) which will use all of the variables whose names start with VARNAME . So if the only variable starting with listed characters are what you need the : list indentifier simplifies the code and will handle the results of Proc Transpose output without having to change the code.

[Ksharp]

Maybe I don't understand your question completely.

You could use colon operator to represent all the variables which starts with VISIT. Like 

data calc;
	set pt_visits_wide;
	last_visit=max(of visit:);
	days_since_last_visit=today()-max(of visit:);
	format last_visit mmddyy10.;
run;

[Ksharp]

Or could be more succinct code by SQL.

data pt_visits;
INPUT patient $ visit_date :MMDDYY10.;
FORMAT visit_date MMDDYY10.;
datalines;
A 01/12/2026
A 01/16/2026
A 02/04/2026
B 01/05/2026
B 01/09/2026
B 01/10/2026
B 01/18/2026
B 01/31/2026
B 02/05/2026
;
run;

proc sql;
create table calc as
select patient,max(visit_date) as last_visit format=mmddyy10.,today()-calculated last_visit as days_since_last_visit
 from pt_visits
  group by patient
;
quit;

[PaigeMiller]

Strongly agreeing with @ballardw who said:

Often making "wide" data such as your transposes is not needed.

If you are careful with your variable names you can use MAX (of varname: ) which will use all of the variables whose names start with VARNAME . So if the only variable starting with listed characters are what you need the : list indentifier simplifies the code and will handle the results of Proc Transpose output without having to change the code.

Rarely is transposing the best solution, and it makes your programming more difficult.