DATA Step, Macro, Functions and more

Matrix to Column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Matrix to Column

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

Accepted Solutions
Solution
‎05-18-2016 12:12 PM
Super Contributor
Posts: 308

Re: Matrix to Column

Posted in reply to Bernardita

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;

View solution in original post


All Replies
Solution
‎05-18-2016 12:12 PM
Super Contributor
Posts: 308

Re: Matrix to Column

Posted in reply to Bernardita

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;
Occasional Contributor
Posts: 11

Re: Matrix to Column

Thank you. It's perfect!
Super User
Super User
Posts: 7,942

Re: Matrix to Column

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.

Occasional Contributor
Posts: 11

Re: Matrix to Column

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

Occasional Contributor
Posts: 11

Re: Matrix to Column

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.

Super User
Super User
Posts: 7,942

Re: Matrix to Column

Posted in reply to Bernardita

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.

Occasional Contributor
Posts: 11

Re: Matrix to Column

Posted in reply to 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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 497 views
  • 3 likes
  • 3 in conversation