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
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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.