data management - Creating new variables using bysort and generate

[Amal Khanolkar]

Hi All

Currently working with a longitudinal dataset, where a subject may have several clinic visits in a given clander year. Additionally, the subject maybe have several clinic visits over several years:

ID j N  clinicdate
1 1  9   23jan2006
1 2  9   23jan2006
1 3  9   23jan2006
1 4  9   23jan2006
1 5  9   23jan2007
1 6  9   23jan2007
1 7  9   23jan2007
1 8  9   23jan2008
1 9  9   23jan2008
2 1  7   23jan2006
2 2  7   23jan2006
2 3  7   23jan2006
2 4  7   23jan2007
2 5  7   23jan2008
2 6  7   23jan2008
2 7  7   23jan2008

Above, ID is the id number assigned to each subject. j is the visit/observatio number for a particular subject in the dataset, whereas N indicates the total number of visits or observations a subject has.

for j:

bysort id (clinicdate): gen j= _n

for N:

bysort id: gen N= _N

However, I would like to create a variable j2 that indicates the visit numbers for each subject but by calender year (and its corresponding N) like below:

ID j N  clinicdate j2 N2
1 1  9   23jan2006  1  4
1 2  9   23jan2006  2  4
1 3  9   23jan2006  3  4
1 4  9   23jan2006  4  4
1 5  9   23jan2007  1  3
1 6  9   23jan2007  2  3
1 7  9   23jan2007  3  3
1 8  9   23jan2008  1  2
1 9  9   23jan2008  2  2
2 1  7   23jan2006  1  3
2 2  7   23jan2006  2  3
2 3  7   23jan2006  3  3
2 4  7   23jan2007  1  1
2 5  7   23jan2008  1  3
2 6  7   23jan2008  2  3
2 7  7   23jan2008  3  3

How would go about creating j2 and N2?

Thanks!

/Amal

[Jorrit Gosens]

This would entail extracting the year from your clinicdate, and then doing 'bysort id year: gen N'
How to extract the year depends on the type of the variable you have.

If its a string currently, do:

gen date = date(x, "DMY",2000)
gen yearr=year(date)
bysort id year: gen j2 = _n
bysort id year: gen N2 = _N

If its not, and to make future answers more directly applicable to your datasets, and make it easier for others to answer your question, please post data examples with dataex, i.e:

ssc install dataex
dataex in 1/20

[Daniel Bela]

Assuming your variable clinicdate is a date variable: Create a variable containing the year, and include this in your bysort statement:

generate year=year(clinicdate)
bysort id year (clinicdate) : generate j2=_n
bysort id year (clinicdate) : generate N2=_N

If it is not, convert clinicdate to a date variable beforehand:

generate date_clinicdate=date(clinicdate,"DMY")
format date_clinicdate %td

Regards
Bela

PS: Please consider using dataex in the future for providing ready-to-use minimal data examples. To install, type ssc install dataex in your Stata command prompt.

[Daniel Bela]

Argh, 2 minutes too slow.

[Amal Khanolkar]

Thanks Daniel. Worked like a charm,

I did try something similar to what you suggested, but I wasn't aware that you could have more than one variable after 'bysort' (which is why it didn't work for me).

Clinicdate is indeed a date variable. And I did extract the year from it but I forgot to mention this.

Unfortunately, at our institute Stata is run on an online secure platform and I cannot install user written commands!

Many thanks

/Amal

[Nick Cox]

I wasn't aware that you could have more than one variable after 'bysort'

See

help by

where the syntax is defined in terms of varlist (not varname) and several examples show more than one name.