BookmarkSubscribeRSS Feed
adityapuri
Calcite | Level 5

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 BeginningCarrierCampaignQWERT
6-Oct-14ANetwork Guys70         0.44         0.22         0.19         0.11
6-Oct-14ACollege Football Network0        
6-Oct-14AiPhone 675         0.75         0.53         0.40         0.27
6-Oct-14BNetwork Guys145         0.60         0.33         0.29         0.16
6-Oct-14BCollege Football Network10         0.49         0.24         0.16         0.11
6-Oct-14BiPhone 611         0.30         0.12         0.04         0.07
7-Oct-14CSamsung52         0.35         0.18         0.06         0.07

 

I would like to convert this to:

 

Week BeginningCampaignA_QA_WA_EA_RA_TB_QB_WB_EB_RB_TC_QC_WC_EC_RC_T
6-Oct-14Network Guys70         0.44         0.22         0.19         0.11145         0.60         0.33         0.29         0.16     
6-Oct-14College Football Network0        10         0.49         0.24         0.16         0.11     
6-Oct-14iPhone 675         0.75         0.53         0.40         0.2711         0.30         0.12         0.04         0.07     
7-Oct-14Samsung          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.

 

 

6 REPLIES 6
ballardw
Super User

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.

adityapuri
Calcite | Level 5

Hi Ballardw - I plan on running a time series regression after transposing the data. For that transposing will be essential.

ballardw
Super User

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.

adityapuri
Calcite | Level 5

Thanks but I need the data structured that way for a few more reasons including data lanscaping...

Reeza
Super User

Google a paper called A Better Way to Flip - it has a macro that will do this relatively effortlessly. 

Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 6 replies
  • 1130 views
  • 2 likes
  • 4 in conversation