From wide to long data using quarterly data

[Ellen Veelema]

Hi all,

For my (accounting) thesis I have to compose discretionary accruals. The first step of this is changing my data from a wide format into a long format.
My dataset consists of 1400 firm observations with quarterly data ranging from 2015Q1 to 2017Q4. There is still no variable called 'year'.

Form Filing ID	TotalAssets2015Q1	TotalAssets2015Q2
1	xx	xx
2	xx	xx

to this:

Form Filing ID	Year	TotalAssets
1	2015Q1	xx
1	2015Q2	xx
1	etc	xx

I used this command:

reshape long TotalAssets TotalRevenue AccountsReceivable PPE, i(FormFilingID) j(Year)

but then I get the following output:

variable Year contains all missing values
r(498);

I think the problem has to do with there is both year and quarter info in the name..

Can someone please help so I can go on with my data analysis?
Thanks in advance.

[Wessel de Kroo]

Hey,

maybe its an idea to use dataex. This will give us an idea of what data you are working with.

I am assuming the etc stands for matching observations for every year and according quater. I think you will need to reformat your Year variable.

This thread might be able to help you out: https://www.statalist.org/forums/for...quarterly-data

[Nick Cox]

This works:

clear
input FormFilingID    TotalAssets2015Q1    TotalAssets2015Q2
1    34    56
2    78    90
end 

reshape long TotalAssets, i(FormFilingID) j(SQDate) string 

gen QDate = quarterly(SQDate, "YQ")
format QDate %tq 
gen Year = year(dofq(QDate)) 

list

[Ellen Veelema]

Thanks for your quick reply. my data looks like this:

FormFilingID TotalAssets2015Q1 TotalAssets2015Q2 Total Assets2015Q3 to 2017Q4 in same format plus TotalRevenue TotalReceivable and PPE (indeed matching format and time span.)
1 xx xx xx
2 xx xx
3 xx xx
4 xx xx
5 xx xx


I want to get the following 'long' format:

FormFiling ID Year TotalAssets TotalRevenue TotalReceivable PPE
1 2015Q1 xx xx xx xx
1 2015Q2
1 2015Q3
1 2015Q4
1 2016Q1 xx xx xx xx
1 2016Q2 xx xx
1 2016Q3
1 2016Q4
1 2017Q1 xx xx xx xx
1 2017Q2 xx xx
1 2017Q3
1 2017Q4
2

thanks!

[Ellen Veelema]

It worked! many thanks

[Ellen Veelema]

Hi all,

Following my previous questions: I have composed all the variables that I need for my regression analysis, still all in the 'long' format.
I now want to do my regression analysis and I think that I have to change again my data back to the 'wide' format. (when Im doing the regression, I got p values that are all totally not significant.)
My data is ranging from 2015Q1 -2017Q4 and I want to examine whether the effect is larger or smaller from 2017Q1 onwards.

What is the best way of doing that?

Thanks.