topic Re: Proc Transpose/Proc Tabulate Problems in SAS Programming

Proc Transpose/Proc Tabulate Problems

awoods8005 — Thu, 03 Oct 2019 21:05:32 GMT

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!

Re: Proc Transpose/Proc Tabulate Problems

VDD — Thu, 03 Oct 2019 21:53:48 GMT

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

Re: Proc Transpose/Proc Tabulate Problems

Cynthia_sas — Thu, 03 Oct 2019 22:05:07 GMT

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

Re: Proc Transpose/Proc Tabulate Problems

awoods8005 — Fri, 04 Oct 2019 14:44:49 GMT

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?

Re: Proc Transpose/Proc Tabulate Problems

Cynthia_sas — Fri, 04 Oct 2019 14:54:59 GMT

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

Re: Proc Transpose/Proc Tabulate Problems

awoods8005 — Fri, 04 Oct 2019 15:03:13 GMT

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.

Re: Proc Transpose/Proc Tabulate Problems

Tom — Fri, 04 Oct 2019 15:23:25 GMT

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

Re: Proc Transpose/Proc Tabulate Problems

Cynthia_sas — Fri, 04 Oct 2019 15:25:47 GMT

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

Re: Proc Transpose/Proc Tabulate Problems

awoods8005 — Fri, 04 Oct 2019 17:34:02 GMT

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!

Re: Proc Transpose/Proc Tabulate Problems

Claudia_SAS — Mon, 24 Feb 2020 19:12:00 GMT

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!