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;
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;
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.
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.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.