topic Re: How can I tranform a long table to wide table? in SAS Programming

How can I tranform a long table to wide table?

afiqcjohari — Fri, 17 Mar 2017 03:46:55 GMT

data Txn;    
   input Year Month $ TxnAmount TxnType $;    
   datalines; 
2017 Jan 100 F
2017 Jan 100 L
2017 Feb 100 F
2017 Feb 200 L
2017 Feb 300 F
2017 Feb 400 L
; 

/*I want this*/ 
data TxnReport;    
   input TxnType $ Jan2017 Feb2017 ;    
   datalines; 
FL 200 1000 *FL is F and L combined; 
F 100 400
L 100 600
;

Currently, I'm using a mix of PROC SQL to get the summation and then PROC TRANPOSE. And finally a DATA Step to append the tables. Does SAS has a proc for pivot table to get this result in lesser and more readable code?

Re: How can I tranform a long table to wide table?

ArtC — Fri, 17 Mar 2017 05:36:31 GMT

When someone mentions 'pivot table' in SAS i always think of PROC TABULATE. The following code should get close to what you want. The multilabel format allows tabulate to combine the CLASS levels and the NOTSORTED along with the PRELOADFMT preserves the format order. For simplicity I took the text date and built a SAS date so that a data format could be applied.

proc format;
   value $txncode (multilabel notsorted)
   'F','L' = 'FL'
   'F' = 'F'
   'L' = 'L';
   run;

data Txn(keep=date txnamount txntype);    
   input Year Month $ TxnAmount TxnType $;
   dtstr = cats('01',month,year);
   date = input(dtstr,date9.); 
   datalines; 
2017 Jan 100 F
2017 Jan 100 L
2017 Feb 100 F
2017 Feb 200 L
2017 Feb 300 F
2017 Feb 400 L
run; 
proc tabulate data=txn;
   class date / order=internal;
   class txntype/mlf preloadfmt order=data;
   var txnamount;
   table txntype=' ',
         date=' '*txnamount=' '*sum=' '/
         box='Type';
   format txntype $txncode.
          date    monyy7.;
   run;

Re: How can I tranform a long table to wide table?

afiqcjohari — Fri, 17 Mar 2017 07:30:05 GMT

Hi ArtC, thank you for this. It helps to know that there's a more simpler way. Is it possible to get the output of the proc tabulate as it is in a sas dataset format?

When I add the out= option, the table has a different structure though.

Re: How can I tranform a long table to wide table?

andreas_lds — Fri, 17 Mar 2017 09:42:36 GMT

Instead of proc tabulate use:

proc summary data=work.txn nway;   
   class TxnType / mlf preloadfmt order=data;
   class date;
   var TxnAmount;
   format txntype $txncode.;
   output out=work.summed(drop=_:) sum=;
run;



proc transpose data=work.summed out=work.want(drop=_name_);
   by TxnType notsorted;
   var TxnAmount;
   id date;
   format date monyy7.;
run;

Re: How can I tranform a long table to wide table?

afiqcjohari — Fri, 17 Mar 2017 10:11:16 GMT

This is pretty much the solution I'm currently using though I didn't use proc format as to avoid using options.
But thanks!

Re: How can I tranform a long table to wide table?

afiqcjohari — Fri, 17 Mar 2017 10:14:17 GMT

I think I have the answer. With proc tabulate, I'm not able to get the output in sas dataset format as it's not a sas table. Is it possible to export the report into Excel?
The table must be as it is.

Re: How can I tranform a long table to wide table?

ballardw — Fri, 17 Mar 2017 15:22:01 GMT

@afiqcjohari wrote:
Hi ArtC, thank you for this. It helps to know that there's a more simpler way. Is it possible to get the output of the proc tabulate as it is in a sas dataset format?

When I add the out= option, the table has a different structure though.

The structure of the OUT= dataset from proc tabulate has to handle nested data in both rows and columns, stacked data and the result of multiple tables from a single procedure call. So there is much information that you likely don't care about. But if your really need a data set

proc transpose data=work.txnout out=work.txnreport (drop=_name_);
  by txntype notsorted ;
  id date;
  var TxnAmount_sum;
run;

where work.txnout would be the name of the OUT= option on tabulate

Ods Excel file="Your excelfile name goes here";

Ods Excel close;

Or Ods tagsets.excelxp instead of Excel if on an earlier version of SAS.

Re: How can I tranform a long table to wide table?

ArtC — Fri, 17 Mar 2017 16:33:30 GMT

if the data set is the objective then i would agree that PROC SUMMARY is better than TABULATE. You can achieve the same levels of summarization either way, but SUMMARY is designed to build the data set while TABULATE is designed to create the table.

Re: How can I tranform a long table to wide table?

ballardw — Fri, 17 Mar 2017 22:38:05 GMT

@afiqcjohari wrote:
I think I have the answer. With proc tabulate, I'm not able to get the output in sas dataset format as it's not a sas table. Is it possible to export the report into Excel?
The table must be as it is.

The output using the OUT= option on Proc Tabulate is a SAS dataset.

It just is in a very specific form for a very specific purpose. I have several processes where I post process data from proc tabulate as it is very much easier than the multiple data step or summary passes and convoluted logic to line things up.

Re: How can I tranform a long table to wide table?

ArtC — Sat, 18 Mar 2017 04:49:54 GMT

@afiqcjohari Since we are providing various alternatives another very flexible way to summarize and tranform in the same step is with PROC REPORT. Although i believe used less often to create a data set, it also has an OUT= option. In each of the three methods that have been presented to you the MLF option is an alternative way to summarize across CLASS levels.

proc report data=txn out=wide;
   column txntype date,txnamount;
   define txntype / group f=$txncode. mlf preloadfmt order=data;
   define date / across f=monyy. order=internal;
   define txnamount / sum;
   run;