topic How to create columns and transpose at the same time? in SAS Procedures

How to create columns and transpose at the same time?

adityapuri — Wed, 23 Mar 2016 21:11:34 GMT

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.

Re: How to creating columns and transpose at the same time?

ballardw — Wed, 23 Mar 2016 19:54:38 GMT

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.

Re: How to creating columns and transpose at the same time?

adityapuri — Wed, 23 Mar 2016 21:12:55 GMT

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

Re: How to creating columns and transpose at the same time?

ballardw — Wed, 23 Mar 2016 23:06:50 GMT

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.

Re: How to create columns and transpose at the same time?

Reeza — Thu, 24 Mar 2016 00:41:14 GMT

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

Re: How to create columns and transpose at the same time?

Ksharp — Thu, 24 Mar 2016 05:47:27 GMT

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;

Re: How to creating columns and transpose at the same time?

adityapuri — Thu, 24 Mar 2016 18:58:01 GMT

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