CODEWITHSUNDEEP
sas
Saurabh
Saurabh

Reputation: 51

SQL/SAS Coding Doubt

I have a data set which is in the following format: 

Customer_ID Var1 Var2 Marketing_Channel  
  1          B    C         D  
  1          B    C         E  
  2          F    G         H  
  2          F    G         I  
  2          F    G         J

I want the result in the following Format: 

Customer_ID Var1 Var2 Marketing_Channel Marketing_Channel1 Marketing_Channel2  
  1          B    C         D   E  
  2          F    G         H   I   J

So, In short I want to get only one row for a customer_ID wherein all possible marketing channels of that customer is listed. I want to do this in SQL/SAS. How can I do this?
Any help will be appreciated

Upvotes: 0

Views: 64

Answers (3)

Tom
Tom

Reputation: 51566

Use PROC TRANSPOSE for this.

proc transpose data=have out=want prefix=marketing_channel;
   by customer_id var1 var2;
   var marketing_channel;
run;

Upvotes: 2

Dinesh
Dinesh

Reputation: 239

SELECT Customer_ID, Var1, Var2,COLLECT(Marketing_Channel) AS Channels FROM table_name GROUP  BY Customer_ID;

I hope this will answer your question.

Upvotes: -1

P.Windridge
P.Windridge

Reputation: 246

This is known as "reshaping" your data from "long" to "wide" format.

In native SAS you can use proc transpose. Check SAS docs. See also http://ats.ucla.edu/stat/sas/modules/longtowide_data.htm

[Not sure about the easiest way if you insist on SQL, sorry. You may have better responses on another StackExchange site, as your question is more about coding than statistical methods.]

Upvotes: 0

Related Questions