BookmarkSubscribeRSS Feed
afiqcjohari
Quartz | Level 8
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?

9 REPLIES 9
ArtC
Rhodochrosite | Level 12

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;
afiqcjohari
Quartz | Level 8
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.
andreas_lds
Jade | Level 19

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;
afiqcjohari
Quartz | Level 8
This is pretty much the solution I'm currently using though I didn't use proc format as to avoid using options.
But thanks!
ballardw
Super User

@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";

<proc tabulate  code goes here>

Ods Excel close;

 

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

afiqcjohari
Quartz | Level 8
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.
ArtC
Rhodochrosite | Level 12

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.

ballardw
Super User

@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.

 

 

ArtC
Rhodochrosite | Level 12

@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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2552 views
  • 5 likes
  • 4 in conversation