Matrix to Column

[Bernardita]

Hello, Please can some one help me to sort out my problem. I would like to Convert a Matrix Data into simply one Column. I have attched an excel example to ilustrate better. Thank you a lot. Bernardita

[Loko · Accepted solution]

Hello,

Many ways to transpose data in SAS.

Here is a datastep solution.

data have;
input HS $ Channel $ Index :$15. Month_Series :$3.  M201601 M201602 M201603 M201604 M201605 M201606 M201607 M201608;
datalines;
B Brokers LRC_Count_R m01 0.989041667 0 0 0 0 0 0 0
B Brokers LRC_Count_R m02 0.978203418 0.989041667 0 0 0 0 0 0
B Brokers LRC_Count_R m03 0.967483939 0.978203418 0.989041667 0 0 0 0 0
B Brokers LRC_Count_R m04 0.956881928 0.967483939 0.978203418 0.989041667 0 0 0 0
B Brokers LRC_Count_R m05 0.946396097 0.956881928 0.967483939 0.978203418 0.989041667 0 0 0
B Brokers LRC_Count_R m06 0.936025173 0.946396097 0.956881928 0.967483939 0.978203418 0.989041667 0 0
B Brokers LRC_Count_R m07 0.925767897 0.936025173 0.946396097 0.956881928 0.967483939 0.978203418 0.989041667 0
B Brokers LRC_Count_R m08 0.915623024 0.925767897 0.936025173 0.946396097 0.956881928 0.967483939 0.978203418 0.989041667
B Brokers LRC_Count_R m09 0.905589321 0.915623024 0.925767897 0.936025173 0.946396097 0.956881928 0.967483939 0.978203418
B Brokers LRC_Count_R m10 0.895665572 0.905589321 0.915623024 0.925767897 0.936025173 0.946396097 0.956881928 0.967483939
;
run;

data want;
set have;

array tr{*} M201601--M201608;

do i=1 to dim(tr);
Series_2=vname(tr{i});
Ind=tr{i};
output;
end;

drop M201601--M201608 i;
run;

[Bernardita]

Thank you. It's perfect!

[RW9]

To note on that, it is advisable - to make your life programming easier, to normalise that data.  I.e. have one row for each month/year and result:

TIMEPOINT    RESULT

201601           0.9

201602           0.6

...

This makes programming much easier - the structure of the dataset doesn't change each time based on the data (as the months and years is data).  Also keeps the number of columns down to a reasonble size.  You can always transpose this data back up for a report if you need to.

[Bernardita]

Thank you for your advice. Indeed the real problem is to calculate a compound Index.
For example: I have a Started value of 3,000 for Jan. I need to extend the Series by Months to Dec, with an assumption of a decrease each month by 1.3%.
This is another way of seeing it.
Thank you a lot,
Bernardita

[Bernardita]

Thank you for your advice. Yes indeed this is a long way of doing something that I believe it can be a lot simpler. I will try to see it in a simple way.

[RW9]

Hi,

I am afraid attaching Excel sheets is not a good idea, I wouldn't download them as they are a security risk.  Please post test data, in the form of a datastep (to avoid us having to type it all out), and an example output of what you want.

[Bernardita]

Hello,

Apologies for attaching an Excel file. Only because the browser at work is not supported by SAS.

I have received a reply though and it worked perfectly. It's what's I needed. 

Thank you very much for your help.

Bernardita