Help in Reshaping the Data

[Hassan Raza]

I have data into this format

* Example generated by -dataex-. To install: ssc install dataex
clear
input str40 companylevel str121 itemname float(year1 year2 year3 year4 year5)
"340007 - Ahmed Hassan Textile Mills Ltd." "         P1. Net Profit  margin / Net profit to sales (F10 as % of F1)"                                                    -.52 -.09   .2  .56 1.65
"340007 - Ahmed Hassan Textile Mills Ltd." "         P2. Asset turnover (F1 to Avg {Current year(A+B),previous year (A+B)})"                                            .94 1.04 1.23  1.6 1.57
"340007 - Ahmed Hassan Textile Mills Ltd." "         P3. Return on Assets  (F10 as a % of Avg {Current year(A+B),previous year (A+B)}"                                 -.49 -.09  .24   .9 2.59
"340007 - Ahmed Hassan Textile Mills Ltd." "         P4. Financial leverage (Avg. {Current year(A+B),previous year (A+B) to Avg. Current year(C),previous year (C))})" 1.95 2.03  2.2 2.35 2.41
"340007 - Ahmed Hassan Textile Mills Ltd." "         P5. Return on equity (F10 as % of Avg {Current year(C),previous year (C)}"                                        -.96 -.18  .53 2.12 6.25
end

i want to convert this data into

* Example generated by -dataex-. To install: ssc install dataex
clear
input str40 compnayname int year float(p1netprofitmarginnetprofittosale p2assetturnoverf1toavgcurrentyea p3returnonassetsf10asaofavgcurre)
"340007 - Ahmed Hassan Textile Mills Ltd." 2016 -.52  .94 -.49
"340007 - Ahmed Hassan Textile Mills Ltd." 2017 -.09 1.04 -.09
"340007 - Ahmed Hassan Textile Mills Ltd." 2018   .2 1.23  .24
"340007 - Ahmed Hassan Textile Mills Ltd." 2019  .56  1.6   .9
"340007 - Ahmed Hassan Textile Mills Ltd." 2020 1.65 1.57 2.59
end

Kindy help me in this regard

[Nick Cox]

This may help

* Example generated by -dataex-. To install: ssc install dataex
clear
input str40 companylevel str121 itemname float(year1 year2 year3 year4 year5)
"340007 - Ahmed Hassan Textile Mills Ltd." "         P1. Net Profit  margin / Net profit to sales (F10 as % of F1)"                                                    -.52 -.09   .2  .56 1.65
"340007 - Ahmed Hassan Textile Mills Ltd." "         P2. Asset turnover (F1 to Avg {Current year(A+B),previous year (A+B)})"                                            .94 1.04 1.23  1.6 1.57
"340007 - Ahmed Hassan Textile Mills Ltd." "         P3. Return on Assets  (F10 as a % of Avg {Current year(A+B),previous year (A+B)}"                                 -.49 -.09  .24   .9 2.59
"340007 - Ahmed Hassan Textile Mills Ltd." "         P4. Financial leverage (Avg. {Current year(A+B),previous year (A+B) to Avg. Current year(C),previous year (C))})" 1.95 2.03  2.2 2.35 2.41
"340007 - Ahmed Hassan Textile Mills Ltd." "         P5. Return on equity (F10 as % of Avg {Current year(C),previous year (C)}"                                        -.96 -.18  .53 2.12 6.25
end

replace itemname = trim(itemname)
gen which = substr(itemname, 1, strpos(itemname, ".") - 1)

forval j = 1/5 { 
    local label`j' = substr(itemname[`j'], 5, .)
}

drop itemname 

reshape long year, i(companylevel which) j(when)
reshape wide year, i(companylevel when) j(which) string 

rename (year*) (*)
rename when year 
replace year = year + 2015 

forval j = 1/5 { 
    label var P`j' "`label`j''"
}

list

describe

. list

     +------------------------------------------------------------------------------------+
     |                             companylevel   year     P1     P2     P3     P4     P5 |
     |------------------------------------------------------------------------------------|
  1. | 340007 - Ahmed Hassan Textile Mills Ltd.   2016   -.52    .94   -.49   1.95   -.96 |
  2. | 340007 - Ahmed Hassan Textile Mills Ltd.   2017   -.09   1.04   -.09   2.03   -.18 |
  3. | 340007 - Ahmed Hassan Textile Mills Ltd.   2018     .2   1.23    .24    2.2    .53 |
  4. | 340007 - Ahmed Hassan Textile Mills Ltd.   2019    .56    1.6     .9   2.35   2.12 |
  5. | 340007 - Ahmed Hassan Textile Mills Ltd.   2020   1.65   1.57   2.59   2.41   6.25 |
     +------------------------------------------------------------------------------------+

. 
. describe 

Contains data
 Observations:             5                  
    Variables:             7                  
-----------------------------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
-----------------------------------------------------------------------------------------------------
companylevel    str40   %40s                  
year            int     %10.0g                
P1              float   %9.0g                 Net Profit margin / Net profit to sales (F10 as % of
                                                F1)
P2              float   %9.0g                 Asset turnover (F1 to Avg {Current year(A+B),previous
                                                year (A+B)})
P3              float   %9.0g                 Return on Assets (F10 as a % of Avg {Current
                                                year(A+B),previous year (A+B)}
P4              float   %9.0g                 Financial leverage (Avg. {Current year(A+B),previous
                                                year (A+B) to Avg. Current
P5              float   %9.0g                 Return on equity (F10 as % of Avg {Current
                                                year(C),previous year (C)}
-----------------------------------------------------------------------------------------------------
Sorted by: companylevel
     Note: Dataset has changed since last saved.

[Hassan Raza]

Thank you so much