I apologize if this has been asked earlier. However, I would like to create and transpose additional columns. My current data set looks like this:
Week Beginning | Carrier | Campaign | Q | W | E | R | T |
6-Oct-14 | A | Network Guys | 70 | 0.44 | 0.22 | 0.19 | 0.11 |
6-Oct-14 | A | College Football Network | 0 | ||||
6-Oct-14 | A | iPhone 6 | 75 | 0.75 | 0.53 | 0.40 | 0.27 |
6-Oct-14 | B | Network Guys | 145 | 0.60 | 0.33 | 0.29 | 0.16 |
6-Oct-14 | B | College Football Network | 10 | 0.49 | 0.24 | 0.16 | 0.11 |
6-Oct-14 | B | iPhone 6 | 11 | 0.30 | 0.12 | 0.04 | 0.07 |
7-Oct-14 | C | Samsung | 52 | 0.35 | 0.18 | 0.06 | 0.07 |
I would like to convert this to:
Week Beginning | Campaign | A_Q | A_W | A_E | A_R | A_T | B_Q | B_W | B_E | B_R | B_T | C_Q | C_W | C_E | C_R | C_T |
6-Oct-14 | Network Guys | 70 | 0.44 | 0.22 | 0.19 | 0.11 | 145 | 0.60 | 0.33 | 0.29 | 0.16 | |||||
6-Oct-14 | College Football Network | 0 | 10 | 0.49 | 0.24 | 0.16 | 0.11 | |||||||||
6-Oct-14 | iPhone 6 | 75 | 0.75 | 0.53 | 0.40 | 0.27 | 11 | 0.30 | 0.12 | 0.04 | 0.07 | |||||
7-Oct-14 | Samsung | 52 | 0.35 | 0.18 | 0.06 | 0.07 |
Is there a quick way of doing this?
I know I can do this using proc sql but that would require me to know the name of the variables. However if I want to create macros then proc transpose or something else like that would be a better way.
Any help would be appreciated.
Thanks in advance.
What will you be doing with that dataset afterwards?
The reason you claim you don't want to use SQL, "know the names of the variables" will be magnified many times especially if you more than a couple levels to your carrier variable.
Hi Ballardw - I plan on running a time series regression after transposing the data. For that transposing will be essential.
You might examine the syntax of the timeseries regression you are going to do. If you need analysis at the Campaign level, it is very likely that you have an option of using BY Campaign to get a regression for each level of campaign. Carrier would then be one of your categorical variables or possibly additional BY variable.
Thanks but I need the data structured that way for a few more reasons including data lanscaping...
Google a paper called A Better Way to Flip - it has a macro that will do this relatively effortlessly.
Check the MERGE skill Me ,Matt and Arthur.T proposed. http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf data have; infile cards expandtabs truncover; input Week : date9. (Beginning CarrierCampaign) (& $40.) Q W E R T0; format Week date9.; cards; 6-Oct-14 A Network Guys 70 0.44 0.22 0.19 0.11 6-Oct-14 A College Football Network 0 6-Oct-14 A iPhone 6 75 0.75 0.53 0.40 0.27 6-Oct-14 B Network Guys 145 0.60 0.33 0.29 0.16 6-Oct-14 B College Football Network 10 0.49 0.24 0.16 0.11 6-Oct-14 B iPhone 6 11 0.30 0.12 0.04 0.07 7-Oct-14 C Samsung 52 0.35 0.18 0.06 0.07 ; run; proc sql; select distinct catt('have(rename=(Q=',Beginning,'_Q W=',Beginning,'_W E=',Beginning,'_E R=',Beginning,'_R T0=',Beginning,'_T0) where=(Week=',Week,' and CarrierCampaign="',CarrierCampaign,'" and Beginning="',Beginning,'"))') into : merge separated by ' ' from have; quit; data want; merge &merge; by Week CarrierCampaign; drop Beginning ; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.