topic Re: Matrix to Column in SAS Programming

Matrix to Column

Bernardita — Thu, 12 May 2016 13:29:30 GMT

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

Re: Matrix to Column

Loko — Thu, 12 May 2016 13:41:35 GMT

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;

Re: Matrix to Column

RW9 — Thu, 12 May 2016 13:46:45 GMT

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.

Re: Matrix to Column

Bernardita — Thu, 12 May 2016 13:53:13 GMT

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

Re: Matrix to Column

Bernardita — Thu, 12 May 2016 13:54:01 GMT

Thank you. It's perfect!

Re: Matrix to Column

RW9 — Thu, 12 May 2016 14:04:50 GMT

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.

Re: Matrix to Column

Bernardita — Thu, 12 May 2016 14:18:55 GMT

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

Re: Matrix to Column

Bernardita — Thu, 12 May 2016 14:23:24 GMT

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.