How to combine rows?

[Jouni Kroger]

Hi,

I´ve been reading help-files and this forom, but I haven´t been able to find a solution. to my problem. I´m birring data to Stata 13.1 from Excell. Now there are more that 22 000 rows and counting.

in Stata I need to analyse data by variable id and dymmy groups and also over year. Like is there change with in some operation goups in or-time, or in ICU cost.

In Stata I need to change it to long panel form so that type_of_activity is sum of each type. Is this even posible or shoud I go first from Excell to database program like Access? And make there a query that makes a new tabel and bring that to Stata.

id	type_of_activiti	Code_of_activity	price	amount	summ	pvm
1	L	P -INR	3,5	1	3,5	22.2.2015
1	Z	Z0005A	758	1	758	22.2.2015
1	L	P -VRAb-O	8	1	8	22.2.2015
1	L	P -Krea	2,2	1	2,2	22.2.2015
1	L	P -Mg	5	1	5	22.2.2015
1	L	P -Na	2	1	2	22.2.2015
1	L	P -TnT	7,5	1	7,5	22.2.2015
1	L	P -Urea	1,9	1	1,9	22.2.2015
1	L	P -K	2	1	2	22.2.2015
1	L	Pt-NOtto1	8	1	8	22.2.2015
1	R	GA4YT	78,3	1	78,3	22.2.2015
1	R	GD1AA	36,46	1	36,46	22.2.2015
1	R	GD1UA	42,53	1	42,53	22.2.2015
1	TA	IMPL	2000	1	2000	22.2.2015
1	V	VKYSOCTAPL	98	1	98	22.2.2015
1	F	Pt-EKG-12	15	1	15	22.2.2015
1	L	P -Gluk	1,9	1	1,9	22.2.2015
1	L	P -APTT	10,8	1	10,8	22.2.2015
1	TP	FMD10	6752	1	6752	22.2.2015
1	L	aB-VeKaasL	9,3	1	9,3	22.2.2015
1	F	Pt-EKG-12	30	1	30	22.2.2015
1	L	P -CK	1,8	1	1,8	22.2.2015
1	L	P -CRP	2,1	1	2,1	22.2.2015
1	H	H00000	0	1	0	22.2.2015
1	H	H20402	679	1	679	22.2.2015
1	H	H20402	679	6	4074	22.2.2015
1	G	284	65	0,5	32,5	22.2.2015
1	L	aB-VeKaasL	6,1	1	6,1	22.2.2015
1	G	284	65	0,75	48,75	22.2.2015
1	L	B -La	4,5	1	4,5	22.2.2015
1	L	P -Bil	1,9	1	1,9	22.2.2015
1	L	B -PVK+T	2,8	1	2,8	22.2.2015
1	L	E -ABORh	10,2	1	10,2	22.2.2015
1	L	E -VRTark	8	1	8	22.2.2015
1	L	P -AFOS	1,8	1	1,8	22.2.2015
1	L	P -ALAT	1,9	1	1,9	22.2.2015
1	L	B -PVK+T	1,7	1	1,7	22.2.2015
1	L	P -CK-MBm	6,3	1	6,3	22.2.2015
1	I	KOKTEHO	3048	1	3048	22.2.2015
2	L	P -Bil	1,9	1	1,9	7.10.2015

Best regadrs,

jouni

[Stephen Jenkins]

Welcome to Statalist. Please read the FAQ (hit the black bar at the top of the page), and note the tips about how to post effectively and how to report sample data within "CODE" delimiters. All this is to maximize the chances of getting a helpful answer. To me, for instance, it is unclear what you are trying to achieve. Your remarks in the second paragraph don't relate clearly to the sample data that you've posted. (Don't get me wrong -- it's great to post sample data, but using CODE delimiters and dataex (from SSC) makes them more legible and usable by other readers.) It is also unclear whether your data snippet is pasted from MS Excel or from Stata.

I am confident that you achieve all that you wish to do by getting the data into Stata (import excel ??) and then manipulating them. My guess is that commands like collapse or contract may be what you want. Or perhaps you can achieve things using a combination of bysort id: egen ... I would recommend reading the help files and manual entries on these commands.

[Jouni Kroger]

Thank you Stephen,

I try to reform my question.


I have panel data set with folloing varlist:

ID numeric
type_of_activiity categorical variable values from 1 to 24 labeled with letter as above.
summ numeric Continuous
few dymmy variables

I tried, after Stephens advise to use

collapse (sum) summ, by (id)

this woks as it shoud.

The problem is.

I need to collaple sub-totals of type_of_activiity by each id. At the same time all other variables like dymmys fmd00, fmd10, fmd22 and fmd24 shoud stay in plase.


I hope, that is beter formed question now.

Jouni

[Jouni Kroger]

Hi again,

Proses is coing forward and now this part of collapse by two variables is solved.

 
collapse (sum) summ, by (id type_of_activiity)

Works as it should work.

Problem. How to keep those dummu variables included?

Best regards,

Jouni

[Jouni Kroger]

Problem solved,

collapse (sum) summ fmd00 fmd10 fmd22 fmd24, by (id type_of_activiity)

Now I can change panel data to wide format and run analysis:

Best regards,

jouni

[Clyde Schechter]

Now I can change panel data to wide format and run analysis:

I wonder what analysis you have in mind here. Nearly all Stata analyses are more easily done in long layout than wide. There are a few exceptions, but only a few.

[Jouni Kroger]

Hi,

Clyde. I was doing xtreg analysis and t-tests in Stata but after that I wanted to make diagram in Excel where it it so easy. In Eexcel wide format is easier. Allso this was exercise to learn more about wide- and long form.