BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bernardita
Fluorite | Level 6
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
1 ACCEPTED SOLUTION

Accepted Solutions
Loko
Barite | Level 11

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

7 REPLIES 7
Loko
Barite | Level 11

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
Fluorite | Level 6
Thank you. It's perfect!
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6
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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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