Help Needed with Reshape Wide Error - "variable counter not found r(111)"

[Sandy Bottoms]

Hello Stata Community,

I am having an issue with reshaping my dataset from long to wide format. I've encountered an error message that I'm struggling to understand and resolve. Below, I provide the sequence of commands I used and the error message I received. Any guidance or suggestions would be greatly appreciated.

I am working with a dataset 'arv.dta' which contains records of visits for patients. Each patient is identified by 'nupn', and there can be multiple visits per patient on different dates. My goal is to reshape the data so that each 'nupn' has a wide entry with all visits.

Here's the code I'm using:


use arv.dta, clear
sort nupn
describe

* Convert string datetime variable to a numeric datetime
gen new_datetime = clock(SC_ARV_visitdate, "MDYhms")
format new_datetime %tc

* Create a visit counter
bysort nupn (new_datetime): gen counter = _n

* Attempt to reshape data
reshape wide *, i(nupn) j(counter)

* Save the transformed dataset
save arv_wide.dta, replace




Upon running the reshape command, I receive the following error message:

variable counter not found r(111);


This error occurs even though the 'counter' variable is created without issue.

Could someone please advise on what might be causing this error and how to fix it? I have confirmed that the 'counter' variable exists and is correctly numbered before attempting the reshape.

Thank you in advance for your time and assistance.

[Nick Cox]

Although it may not be biting you should always put datetimes into a double -- as stressed several times in help datetime

gen double new_datetime = clock(SC_ARV_visitdate, "MDYhms")

Otherwise I can't see that reshape wide * is ever correct, if only because * includes the variables that you're specifying to i() and j().

If you want every variable apart from those two that would be yielded by

ds iupn counter. not

reshape wide `r(varlist)' , i(nupn) j(counter)

[Sandy Bottoms]

Hi Nick,
Thanks for you advice.

When I tried to run:
ds nupn counter. not

I got the error:

factor-variable and time-series operators not allowed
r(101);

When I ran:
reshape wide `r(varlist)' , i(nupn) j(counter) I got the error: invalid syntax

[Nick Cox]

We ask that you don't post .dta attachments. In this case also patient data even if anonymised should not normally be posted publicly.

[Leonardo Guizzetti]

There was a slight typo in Nick's suggested code.

ds iupn counter, not

reshape wide `r(varlist)' , i(nupn) j(counter)

[Nick Cox]

Indeed; sorry about the typo. The following command depends on ds working.