topic Re: Transforming Panel Data to Time Series in SAS Programming

Transforming Panel Data to Time Series

rkd84 — Wed, 18 May 2022 06:00:59 GMT

Dear Friends,

I am trying to transform the existing data (Presently in this format):

Name	2013	2014	2015	2016	2017	2018	2019	2020	2021
3M India Ltd.	11.12	10.95	10.7	10.62	10.38	10.25	10.2	11.03	10.66
A B B India Ltd.	7.93	7.93	7.8	7.99	7.8	7.99	7.96	8.32	7.97
A C C Ltd.	2.56	2.61	2.56	2.52	2.51	2.47	2.59	2.71	2.57
A I A Engineering Ltd.	5.33	5.05	4.82	4.82	4.67	4.75	4.68	4.71	4.85
A P L Apollo Tubes Ltd.	8.75	8.52	8.55	8.53	8.35	8.23	8.46	8.45	8.48

To Something like This:

Name	Year	MTBV
3M India Ltd.	2013	11.12
3M India Ltd.	2014	10.95
3M India Ltd.	2015	10.7
3M India Ltd.	2016	10.62
3M India Ltd.	2017	10.38
3M India Ltd.	2018	10.25
3M India Ltd.	2019	10.2
3M India Ltd.	2020	11.03
3M India Ltd.	2021	10.66
A B B India Ltd.	2013	7.93
A B B India Ltd.	2014	7.93
A B B India Ltd.	2015	7.8
A B B India Ltd.	2016	7.99
A B B India Ltd.	2017	7.8
A B B India Ltd.	2018	7.99
A B B India Ltd.	2019	7.96
A B B India Ltd.	2020	8.32
A B B India Ltd.	2021	7.97

I am working on more than 500 stock data as of now. Can someone please suggest an easy and efficient way to do this?

Attaching a sample file for reference.

Thanks in Advance
Ritesh

Re: Transforming Panel Data to Time Series

Kurt_Bremser — Wed, 18 May 2022 06:21:03 GMT

You seem to have an Excel issue, you should ask this question in an Excel-centered support forum.

(Numbers are not valid variable names in SAS)

If you want to do this in SAS, provide a SAS dataset in usable form (data step with datalines).

In SAS, use PROC TRANSPOSE to transpose from wide to long, and a follow-up DATA step to convert _NAME_ to year.

Re: Transforming Panel Data to Time Series

andreas_lds — Wed, 18 May 2022 07:26:11 GMT

Maybe like this:

options validvarname=v7;

proc import datafile="FakeData\TransposeNumNames.xlsx" dbms=xlsx replace out=have;
   sheet= "have";
run;

proc transpose data=have out=transposed(drop=_name_ rename=(col1=mtbv)) label=year;
   by Name;
   var _:;
run;

I won't open any excel files, so i build one using the table you have posted.

Re: Transforming Panel Data to Time Series

rkd84 — Wed, 18 May 2022 07:37:37 GMT

proc transpose data=WD.BVTMV out=T_BVTMV(drop=_name_ rename=(col1=mtbv)) label=year;
by Company_Name;
var _all_;
run;

It worked.

Thanks a ton.