frequency

[Buyadaa Oyunchimeg]

Dear all,
Hope everything goes well with you.
I am new to STATA and having a problem in creating frequency tables.
Dataset looks as below:


input byte(id aspirin2006 clopidogrel2006 statin2006 fibrate2006 aspirin2007 clopidogrel2007 statin2007 fibrate2007 aspirin2008 clopidogrel2008 statin2008 fibrate2008 aspirin2009 clopidogrel2009 statin2009 fibrate2009 aspirin2010 clopidogrel2010 statin2010 fibrat2010) int str12 first_date last_date
1 0 0 0 1 0 0 0 1 0 0 0 0 0 0 1 0 0 1 1 0 2007 2010
2 0 0 1 0 1 0 0 1 0 0 0 0 0 1 0 1 1 0 0 1 2006 2010
3 0 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 0 0 1 0 2007 2009
4 0 0 1 0 0 1 1 0 0 1 0 1 0 0 1 0 0 1 0 0 2006 2010
5 0 0 0 1 0 0 0 1 0 0 0 1 0 1 1 1 1 1 1 1 2007 2009
6 1 1 1 1 1 1 1 0 0 0 0 0 1 0 0 0 1 0 0 1 2006 2010
7 0 0 0 1 0 0 0 1 1 0 0 1 0 0 1 0 0 1 0 0 2006 2009
8 1 0 0 1 0 0 0 0 1 0 1 0 0 1 0 0 1 0 1 0 2007 2010
9 0 0 0 0 0 1 0 1 0 1 0 1 0 1 1 1 1 0 0 0 2006 2010

end

I would like to create the following tables:
Table 1

Year	Patient No	Prescription No		Mean No of prescription per person
Anti-thrombotic	Lipid lowering	Anti-thrombotic	Lipid lowering
2006
2007
2008
2009
2010

table 2

Year	Prescription No
One class (anti-thrombotic or lipid lowering )	Two classes (anti-thrombotic + lipid lowering )
2006
2007
2008
2009
2010

Would really appreciate if you may help me to solve the problem.

Thank you in advance.
Kind regards,
Oyun

[Marcos Almeida]

Welcome to the Stata Forum / Statalist.

Please take some time to read the FAQ, particularly the topics about sharing data/command/output.

That said, it seems you first need to - reshape long - the data set.

[Clyde Schechter]

Well, Marcos is almost right. But before you even -reshape- your data long, you need to correct the spelling of fibrat2010 to fibrate2010, and you need to change first_date from a string to a numeric variable using -destring-. Once you have done that, -reshape- is indeed the next step. Then it's a matter of calculating the relevant variables and applying -collapse-.

clear
input byte(id aspirin2006 clopidogrel2006 statin2006 fibrate2006 aspirin2007 clopidogrel2007 statin2007 fibrate2007 aspirin2008 clopidogrel2008 statin2008 fibrate2008 aspirin2009 clopidogrel2009 statin2009 fibrate2009 aspirin2010 clopidogrel2010 statin2010 fibrate2010) int first_date last_date
1 0 0 0 1 0 0 0 1 0 0 0 0 0 0 1 0 0 1 1 0 2007 2010
2 0 0 1 0 1 0 0 1 0 0 0 0 0 1 0 1 1 0 0 1 2006 2010
3 0 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 0 0 1 0 2007 2009
4 0 0 1 0 0 1 1 0 0 1 0 1 0 0 1 0 0 1 0 0 2006 2010
5 0 0 0 1 0 0 0 1 0 0 0 1 0 1 1 1 1 1 1 1 2007 2009
6 1 1 1 1 1 1 1 0 0 0 0 0 1 0 0 0 1 0 0 1 2006 2010
7 0 0 0 1 0 0 0 1 1 0 0 1 0 0 1 0 0 1 0 0 2006 2009
8 1 0 0 1 0 0 0 0 1 0 1 0 0 1 0 0 1 0 1 0 2007 2010
9 0 0 0 0 0 1 0 1 0 1 0 1 0 1 1 1 1 0 0 0 2006 2010
end

reshape long aspirin clopidogrel statin fibrate, i(id) j(year)
drop if !inrange(year, first_date, last_date)

egen lipid_lowering = rowmax(statin fibrate)
egen anti_thrombotic = rowmax(aspirin clopidogrel)
egen classes = rowtotal(lipid_lowering anti_thrombotic)
gen one_class = classes == 1
gen two_classes = classes == 2

collapse (sum) lipid_lowering - two_classes, by(year)
drop classes
list, noobs clean abbrev(32)

Note: I have not dealt with your "No. of prescriptions" because I don't see that information anywhere in your data.

Added: You have posted with questions about this data set previously. I can almost guarantee you that for any additional questions you have, the answer will begin with -reshape long-. The wide layout you have is of very little use for analysis in Stata. Only a small number of commands will work well with it. So I suggest that you run the -reshape long- and -drop if !inrange...- commands, and then save that as a new data set. Start all your analyses from there, rather than from the original data.

[Buyadaa Oyunchimeg]

Dear prof. Schechter


It works! Thank so much for all your help.


Sincerely,
Oyun