Proc Transpose/Proc Tabulate Problems

[awoods8005]

Hi,

I am trying to summarize data that I used a PIVOT function on in SQL Server. The only problem is that my data has two row identifiers (member_id and month) so it is creating problems with the proc transpose. 

I am trying to get the following data

member_id           month           paid_amount         category

42                        jan                     20.00                         books

398732                     feb                     782.35                  cds

59669                     nov                     394.65                  clothes

42                     jan                     40.00                       books

to transpose and look like a summary per member but using the categories as column headers and summing for each category

so it would look like this

member_id      month            books          cds        clothes        other

42                   jan                   60.00         0           0              0

...etc

If there is a way to do this using only proc transpose or using proc transpose combined with another function as well please let me know.

Also if this is unclear in any way let me know and I will try to provide more information.

I appreciate any help I can get, this seems like a very friendly community from what I can tell.

Thanks!

[VDD]

you need to use the fancy assignments to sum your data for SQL server using the order by statement.

[Cynthia_sas · Accepted solution]

Hi:

  I'm not sure you need PROC TRANSPOSE. I think TABULATE can do this for you. You did not post data in a form that can be used, so I made some fake data. TABULATE worked for me as shown below:

Hope this helps,

Cynthia

[awoods8005]

Great, thank you so much! 

Also, do you have any idea how I can make the output be in a table format so that I can left join it to other tables using proc sql?

[Cynthia_sas]

Hi, in that case, I assume you would not want the TOTAL column on the right or the TOTAL row at the bottom.
Cynthia

[awoods8005]

Yes, I will probably take the total columns/rows off somehow. 

Do you think I would need a proc data command to convert the output to a joinable table?

Some background: I am hoping to join this table to a table with the members location to see if there is any correlation between their spending and location.

[Cynthia_sas]

Hi:

  If you only needed a report, then I would recommend TABULATE. If you need a transposed AND summarized table to use in a join, then I would recommend either a DATA step probably with an ARRAY or just use PROC REPORT with the OUT= option, like this (using the same fakedata and the same PROC SORT):

Then the WORK.SUM_OUT table is the one you use in your merge or join. But I would make sure that your analytic procedure really needs the transposed structure of the data. Many analytic procedures use the data in "long" format, as you show for your data.

Hope this helps,

Cynthia

[Tom]

I am confused.  Your input has two observations for the same persion/month/category.

Do you need to summarize it first?  Is that what the "pivot table" was supposed to do for you?  Why not just use PROC SUMMARY?

data have ;
 input member_id month :$3. paid_amount category :$20.;
cards;
42  jan 20.00 books
398732 feb 782.35  cds
59669 nov 394.65  clothes
42 jan 40.00 books
;

proc summary data=have nway ;
 class member_id month category ;
 var paid_amount ;
 output out=step1 sum= ;
run;

proc transpose data=step1 out=want;
  by member_id month;
  id category;
  var paid_amount;
run;

proc print; run;
 

       member_
Obs       id      month      _NAME_       books    clothes      cds

 1          42     jan     paid_amount      60         .         .
 2       59669     nov     paid_amount       .      394.65       .
 3      398732     feb     paid_amount       .         .      782.35

[awoods8005]

Yes, that worked also thank you.

What I ended up using was a proc sql where I just summarized the data based on category. It looked like this

proc sql;

create table FINAL as

select

memberid

, month

, sum(case when category = 'bookds' then paid_amt else 0 end) as Book_total

...etc...

from previous table

group by memberid, month

order by memberid, month

;quit;

This returned the data in a format that I can left join to another table that has memberid and zip code. From there I am going to try to see if there is any correlation between variables.

Thanks for all the help, I really appreciate it. And if any of you know any good ways to compare multiple variables (zip code) to other dependent variables ( paid amount by category) please let me know I am always interested in learning new things. Thanks!

[Claudia_SAS]

Hello,

What I am trying to do is based on the giving table: t1 (Name(string), Var(string))

Name	Var
A	Var1
A	Var2
A	Var3
B	Var1
B	Var3
B	Var89
C	Var2
D	Var70

In  the end I would like to have my table looking as:

Name	Var1	Var2	Var3	Var4	….	Var70	…	Var89
A	1	1	1	0	0	0	0
B	1	0	1	0	0	0	0	1
C	0	1	0	0	0	0	0	0
D	0	0	0	0	0	1	0	0

Is there an easy way to do in one step using proc sql?

Thank you for your help!