Transpose help

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Transpose help

Hi,

i need to transpose the following file in order to produce a table with columns as the names of the categories for  a university project. The below image is a snap of the original file with over 13000 instances.

Capture.jpg

needed output example

TransactionIDnewinmens-new-inmaxidresseseveningdressesdaytopscasual shortsbikinijelly-sandalsspring-summer-tv-adcardigansjeans
20xxxxxxxxx11
20xxxxxxxxx11
20xxxxxxxxx111

Many thanks

Roy


Accepted Solutions
Solution
‎06-10-2015 11:13 PM
Super User
Posts: 5,082

Re: Transpose help

Assuming that you just want to sum up the data first with proc summary:

proc summary data=have nway;

  var quantity;

  class category;

  by TransactionID;

  output out=sums (keep=TransactionID category quantity) sum=;

run;

Picturing the structure of the output data set from PROC SUMMARY can occasionally be tricky, although this one is straightforward.  It's a skill worth learning.

View solution in original post


All Replies
Super User
Posts: 5,082

Re: Transpose help

SAS can do this fairly easily:

proc transpose data=have out=want;

   var quantity;

   id category;

   by TransactionID;

run;

However, it's important to realize the limitations.

(1) SAS does not permit dashes as part of the variable names.  It will automatically change dashes (as well as any other illegal characters) to underscores.

(2) Your data set has to be sorted by TransactionID for this program to work.

(3) If you have any CATEGORY values longer than 32 characters, they will get truncated.

(4) If you have multiple observations for the same combination of TransactionID and CATEGORY, you will get an error message.  If you think it appropriate, you could get around this by summarizing your data first (coming up with a total QUANTITY for each combination).

(5) This program doesn't print the results ... it creates an output data set holding the results.  If you wanted a report, you still have to print the output data set.

Good luck.

New Contributor
Posts: 2

Re: Transpose help

Thanks ,

all the points that you mentioned are clear and infant point 4 is the problem now. i am getting an error as you mentioned since i have reputations of categories under the same transactionID. any convenient ideas of how i could summarize the data before?

Thanks

Roy

Solution
‎06-10-2015 11:13 PM
Super User
Posts: 5,082

Re: Transpose help

Assuming that you just want to sum up the data first with proc summary:

proc summary data=have nway;

  var quantity;

  class category;

  by TransactionID;

  output out=sums (keep=TransactionID category quantity) sum=;

run;

Picturing the structure of the output data set from PROC SUMMARY can occasionally be tricky, although this one is straightforward.  It's a skill worth learning.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 309 views
  • 1 like
  • 2 in conversation