Trouble understanding Proc Transpose

[lcwrite]

I am trying to learn to use PROC TRANSPOSE. I have used a couple of Udemy SAS courses and they use the contents of SASHELP but somehow I'm missing something.

I have an Excel document with three fields: Date, Store, and Amount. The spreadsheet is a record of purchases made for a hobby I'm trying to make some money from. I buy t-shirts with interesting graphics, like concerts, and cut the graphic out. I wrap the graphic around an artists canvas or other frame so that it can be hung on  wall and take up less space than t-shirts would. 

The PROC IMPORT is fine. The PROC SORT is fine. I sort on a field called 'Store' and there are multiple entries for purchases from some of the stores. There in lies my problem. I want to use PROC TRANSPOSE to make each store name a column and have columns for Date and each store. In each column I want the date and amount of a purchase. Ideally, I'd have a sum at the bottom for each store.

PROC TRANSPOSE complains about the duplicate entries for STORE. 

[PaigeMiller]

I'm going to avoid addressing the question you asked about using PROC TRANSPOSE, and instead give out advice.

DO NOT PERFORM ANY TRANSPOSE ON THIS DATA.

The data set that you explained, with three columns DATE, STORE and AMOUNT, is the perfect data set for you to perform whatever arithmetic or statistical calculations in SAS. It's very easy in SAS to get arithmetic or statistical calculations on your data.

The transposed data set, if you were successful in doing the transpose, will make the rest of your programming very difficult.

So your difficulty in understanding how to perform PROC TRANSPOSE on this data is gone. Don't transpose it.

But to be specific, let's suppose you have a store named "ABC Toothbrushes" and it appears the STORE variable of your existing data set in several observations. You want to transpose it so that ABC Toothbrushes now is a column name, in fact several column names because there are several rows with ABC Toothbrushes. This is impossible in SAS, you cannot have two columns with the same name. Transposing this data makes no sense in SAS, it is completely the wrong thing to do, and stop right there and do NOT do a transpose.

[Patrick · Accepted solution]

Welcome to the SAS Communities. 

With Excel everything is intermingled and you have just "stuff" in sheets. With SAS you need to distinguish between data (tables) and reports. 

With SAS data is normally best organised in a long and narrow structure like you have it already. Most SAS procedures work best with such narrow data. 

Procedures like Proc Tabulate and Proc Report allow you to then generate reports using such data.

Below sample code to illustrate what I'm talking about.

data have;
 infile datalines dsd dlm=',' truncover;
 input Date :ddmmyy10. Store :$50. Amount :dollar16.;
 format date date9.;
 datalines;
5/06/2023,Hobby Lobby,$103.86
9/06/2023,Haltom Thrift,$19.42
10/06/2023,Forever Young,$302.84
10/06/2023,Goodwill Arlington,$16.00
11/06/2023,Lowe's,$23.45
15/06/2023,Hobby Lobby,$29.18
24/06/2023,Michael's,$11.90
25/06/2023,Love It Again,$17.32
25/06/2023,Witherspoon's Antique,$32.48
27/06/2023,Rockler,$145.00
4/07/2023,JoAnne Fabrics,$10.00
4/07/2023,Uptown Cheapskate,$34.61
5/07/2023,Ace Hardware,$34.04
8/07/2023,Hobby Lobby,$34.54
14/07/2023,1/2 of 1/2,$388.69
15/07/2023,Goodwill Benbrook,$48.00
15/07/2023,Goodwill Campus,$29.50
15/07/2023,Hobby Lobby,$29.94
15/07/2023,JoAnne Fabrics,$72.60
15/07/2023,Target,$77.03
18/07/2023,Michael's,$84.89
29/07/2023,Hobby Lobby,$43.22
23/08/2023,The Container Store,$114.68
31/08/2023,Ace Hardware,$81.11
31/08/2023,Home Depot,$256.16
1/09/2023,Michael's,$56.98
2/09/2023,Michael's,$60.58
11/09/2023,Home Depot,$118.80
27/09/2023,Michael's,$140.64
30/09/2023,City of Weatherford,$30.00
7/10/2023,Goodwill Camp Bowie,$17.00
7/10/2023,Goodwill Campus,$3.58
30/10/2023,City of Weatherford,$30.00
1/11/2023,City of Weatherford,$30.00
4/11/2023,Goodwill Camp Bowie,$35.00
24/11/2023,Haltom Thrift,$28.09
1/12/2023,City of Weatherford,$30.00
;

/*ods excel file="c:\temp\my_excel.xlsx";*/
%let sv_missing=%sysfunc(getoption(missing,keyword));
options missing=' ';

proc tabulate data=have;
  class store date;
  var amount;
  keylabel sum=' ';
  table date=' ' all="Total",
        store*amount=' '*f=dollar16.2
  ;
run;

options &sv_missing;
/*ods excel close;*/

[lcwrite]

Thank you,

This is what I was looking for. I've been studying for the Base SAS certificate and I know it has a couple of questions on PROC TRANSPOSE. I took the test and passed a couple of weeks ago but am fairly certain I missed the questions about Transpose and thought this might be a good way to used it. Your solution though is what I would need.

[Tom]

I don't understand what you are saying you want the result to LOOK like. But it really sounds like a REPORT and not a DATASET.  So PROC TRANSPOSE is probably not going to be part of the solution.

[lcwrite]

I wanted the output to look like what Patrick has done with PROC TABULATE.

[Tom]

---

@lcwrite wrote:

I wanted the output to look like what Patrick has done with PROC TABULATE.

---

That you could kind of create with PROC TRANSPOSE. 

You would need to sort by DATE, not STORE.

proc transpose ;
  by date;
  id store;
  var amount;
run;

But the values of STORE will not make very good VARIABLE names.