Proc Transpose And Grouping Issues

[Goose306]

Hi all,

I'm attempting to use a proc transpose to move data from a tall set, to a wide set. There is a fairly intensive amount of categories, but the original tall set isn't too large (~74k rows). Here's an example of what said data would look like (I can't share the actual data due to sensitive info) :

What I want to do is transpose it so the dates become columns, with the spend as each one in its own row, so something like this:

The issue is, whenever I follow any sort of instructions I find anywhere for Transposing, I end up with data that looks like this:

The issue it appears is it isn't collapsing down as it transposes, instead just moving each data point for each day over into a separate column in the same row it was previously. My actual data set isn't this neat (as can be expected from about 70k+ lines, which is 30 days of data) but I can remove the identifiers of spend and get only unique values and get it below 30k.

I'm a fairly novice programmer in SAS, but I haven't seen this behavior mentioned on any of the transposing tutorials I was following. Any assistance would be helpful, as I'm sure its an easy, obvious issue. Here's my existing SQL (changed to match example rather than my own sensitive data) :

PROC SQL;
     CREATE VIEW WORK.TransposedData AS
           SELECT T.Category1, T.Category2, T.Category3
     FROM WORK.SpendData(FIRSTOBS=1 ) as T
;
QUIT;
PROC TRANSPOSE DATA=TransposedData
     OUT=WORK.TransposedData(LABEL="TransposedData")
     NAME=Source
     LABEL=Label
;
     BY Category1 NOTSORTED Category2 NOTSORTED Category3 NOTSORTED;
     VAR Spend1 Spend2;
     ID   Date;

Thanks for any help possible! I am on SAS EG 7.1, for what it's worth.

[Astounding · Accepted solution]

With two variables in the VAR statement of PROC TRANSPOSE, I don't know of a way to get the result you want directly.  However, you could turn the result into what you want with this:

proc sort data=TransposedData;

by category1 category2 category3 spendtype;

run;

data want;

update transposeddata (obs=0) transposeddata;

by category1 category2 category3 spendtype;

run;

Your data sets are small enough that this should be viable.  Good luck.

[Goose306]

Thanks for the reply. That seemed to have sorted the stubborn data as I needed.

Regarding the two spend categories: I think an easier way will be to break it into two tables then re-merge them together. The main point of needing to massage this data is just make it more logical than a tall list as eventually its going to end up in Excel with a lot of VBA powering it and it needs to be relatively efficent, and so either setting the two spend categories as two separate column sections or as an every other row as I originally intended will make it easier to manage and code for than the current tall data set.

Thanks again for your help!