Data Formatting

[Drew Nelson]

Hi all,

I use STATA to generate reports for the company I work at. Given the increased volume of reporting, I'm attempting to recreate tasks I usually do in Excel with STATA.

Please see dataex below. For each month (Apr2022, May2022...), my goal is to sum the observations if AR_Roll_Forward equals "Gross Charges", "Payments", "Contractual Adjustments", "Other Adj.", "Refunds", and "BadDebt". The sum of those observations would be entered into 'Ending AR' for each month which, for each month, is currently blank.

Once I get the ending AR for each month, I would need to add a new row (set obs `=_N+1') and replace AR_Roll_Forward="Begining AR" if AR_Roll_Forward=="". The information entered into "Begining AR" would be the prior month's "Ending AR". So, for example, Apr2022's 'Ending AR" would be entered into May2022's "Begining AR" and so on.....

Any help would be much appreciated. I've always receieved amazing help and guidance on this forum, so thank you!

* Example generated by -dataex-. For more info, type help dataex
clear
input str23 AR_Roll_Forward double(Apr2022 May2022 Jun2022 Jul2022)
"Gross Charges"            6055836.049999998   5399749.779999999 6203956.3599999985  5246064.499999999
"Pain Charges"             259932.3599999997   257881.4699999997  209112.9499999997 189631.25999999972
"Ortho Charges"            5795903.689999999          5141868.31  5994843.409999999  5056433.239999999
"Payments"                1593138.8200000003          1458100.13 1595957.1300000001 1369654.0999999999
"Contractual Adjustments"         4440878.79  3956573.9799999986  4577486.069999999         3816163.44
"Other Adj."              45483.810000000005 -24082.109999999993 19780.339999999997           30493.94
"Refunds"                           47478.46  15912.390000000001           36514.87 16264.950000000004
"BadDebt"                 19856.760000000002  10771.069999999998 20768.390000000003            9885.99
"Ending AR"                                .                   .                  .                  .
end

[Emil Alnor]

Drew Nelson your data is currently setup with variables in rows and month-observations in coloumns, i.e. it confusing observations with variables, so I begin with transposing your data:

xpose, clear varname
drop in 1
local v gc pc oc p ca oa r bd ear
forval x=1/9{
    gettoken name v : v
    ren v`x' `name'
}
gen ending_ar = gc + p + ca + oa + r + bd
gen beginning_ar = ending_ar[_n-1]

[Drew Nelson]

Hi Emil,

That definitely got me closer - thank you. Ultimately my results need to look like my dataex example, where variables are in rows and month-observations are in columns. I reshaped after running your code and ended up with 166 variables - a variable for each month/variable. Thoughts?