Proc Report - Listing

[deleted_user]

I am trying to write a report that has the dates across the top. I want the variables to be listed down the left, with the data going across. For example...

Jan Feb March April
Trans Amount $40 $65 $76.50 $45.30
Average Percent 60% 35% 37% $76.20
Numer of Trans 16 45 66 54

I have tried several ways using proc report, and I am at a loss. Any suggestions would be much appreciated.

Thanks!
-Alana-

[Cynthia_sas]

Hi:
What does your data look like???? For example, this output was created for 3 months of sales information from SASHELP.PRDSALE. Note the use of ACROSS as the usage for the MONTH variable. Also note the use of CALL DEFINE to alter the format used for the ACTUAL variable based on the value of DIVISION. Your data may or may not be in a similar structure in order for ACROSS to work for you. SASHELP.PRDSALE has one observation for every month and DIVISION has 2 possible values.

cynthia
[pre]
ods html file='c:\temp\prdsale3.html' style=sasweb;

proc report data=sashelp.prdsale nowd;
where quarter = 1 and year = 1994;
column division actual,month;
define division / group;
define month / across order=internal;
define actual / sum;
compute actual;
if division = 'CONSUMER' then
call define(_COL_,'format','dollar15.2');
else if division = 'EDUCATION' then
call define(_COL_,'format','comma15.0');
endcomp;
run;
ods html close;
[/pre]

[deleted_user]

Heh, I tried to type out how I wanted the data formatted, but once posted, it didn't work. Hope the below looks better. I added the dots to keep it looking the way I want:

Basically, I want the date (rolling 13 months) going across.Then I would want the variable listed like this on the left with the data for them going across:

.................................................Jan.........Feb........Mar........Apr
Dollars Spent..............................$56........$77........$34.......$900
Months active..............................10..........01...........99..........56
Number of transactions..................4..........44...........67..........89

I have been trying proc report, but if there is something better to use, I am open to it!

I hope this makes more sense than the previous post.

-Alana-

[Cynthia_sas]

Hi:
In order to preserve indenting in forum postings, you need to use the [pre] and [/pre] tags around your code and output in order to maintain indenting and spacing, as described in this forum posting:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

Understanding what you want to produce is only half of the equation. The other half of the equation is what your starting point looks like. For example, do you have variables named Jan, Feb and March or do you have a MONTH variable -- similar to what I posted for SASHELP.PRDSALE. What do your input observations look like??? Are DOLLARS SPENT, MONTHS ACTIVE and NUMBER OF TRANSACTION on 1 observation or on multiple observations? It could be that you could use PROC TABULATE for this report, but again, it's hard to say without seeing your INPUT data and a sample of variables from your INPUT data.

Since the SASHELP files are available on every installation, however, you should be able to compare the structure of SASHELP.PRDSALE with the structure of your INPUT data. Did you look at the PROC REPORT code that's been posted? Is there a reason a similar program won't work for your data???

Without knowing what your starting point (your input data) looks like, it is impossible to say whether your data needs to undergo any transformations before you pass it to PROC REPORT. Or whether PROC TABULATE might be a better choice.

But since you haven't posted what your INPUT data looks like, it's very hard to make further comments. (Just curious...what happened to the Average Percent row that you originally posted??)

cynthia

[deleted_user]

I start by pulling data using Proc Sql from an oracle table:

[pre]
Proc Sql
CREATE TABLE sasuser.Balance as
SELECT
MONTH,
MONTHS ACTIVE,
NUMBERS OF TRANSACTION ,
DOLLARS SPENT,
AVERAGE PERCENT
FROM
pvt.table
quit;
[/pre]

From there I do a proc summary:
[pre]
proc summary data=sasuser.Balance;
var MONTH MONTHS_ACTIVE NUMBERS_OF_TRANSACTION
DOLLARS_SPENT AVERAGE_PERCENT;
class Dte;
output out=DMYM.Sum_balanace sum=;
run;
[/pre]


Once the data is summarized, I was thinking of doing a proc transpose, so that the data goes left to right. That works, but getting the above format has proven difficult.

I hope this is more detailed for you.

[Cynthia_sas]

Hi:
If you used PROC TABULATE, you might not need PROC SUMMARY at all. If you were going to use PROC REPORT, again, you might not need PROC SUMMARY at all, but you might need to restructure your data so that you have a structure something like this for PROC REPORT:
[pre]
MONTH DESCRIP VALUE
01/01/2010 Dollars Spent 56
02/01/2010 Dollars Spent 77
03/01/2010 Dollars Spent 34
04/01/2010 Dollars Spent 900
01/01/2010 Months Active 10
02/01/2010 Months Active 01
03/01/2010 Months Active 99
04/01/2010 Months Active 56
01/01/2010 Number of Transactions 4
02/01/2010 Number of Transactions 44
03/01/2010 Number of Transactions 67
04/01/2010 Number of Transactions 89
[/pre]

It really depends on how or whether you want to structure your dataset -- whether it will work for TABULATE or REPORT better. The advantage of either TABULATE or REPORT is that the MONTH variable should be able to go across the columns easily either using MONTH in the COL dimension in PROC TABULATE or using MONTH as an ACROSS variable in PROC REPORT.

cynthia

[Peter_C]

probably too late, but this a good example/opportunity to show off tabulate versatility[pre]data sasuser.balance ;
input month MONTHS_ACTIVE NUMBERS_OF_TRANSACTION DOLLARS_SPENT AVERAGE_PERCENT ;
attrib month informat= yymmn6. format=yymmn6.
AVERAGE_PERCENT format= percent10.2 informat= percent10. ;
list;cards;
200908 12 123 1234 8.1%
200909 13 123 2234 7.7%
200910 14 113 1334 6.6%
200911 15 123 1244 5.5%
200912 16 133 1235 8.4%
201001 17 143 6234 3.3%
201002 18 123 7234 8.2%
;

proc tabulate data= sasuser.balance missing ;
class month ;
format month monName3. ;* hoping access to oracle delivers a date value ;
var MONTHS_ACTIVE NUMBERS_OF_TRANSACTION DOLLARS_SPENT AVERAGE_PERCENT ;
table (
DOLLARS_SPENT='Dollars Spent' *f= dollar10. *sum=' '
MONTHS_ACTIVE='Months active' *f= comma10. *sum=' '
NUMBERS_OF_TRANSACTION='Numbers of Transaction'*f= comma10. *sum=' '
AVERAGE_PERCENT='Average Percent' *f= percent10.1*mean=' ' ;
) , month = ' ' / row= float ;
run ;[/pre]
and that provides sas monospace font which does not render well here, so here is the ansi box chars that detract somewhat[pre]
----------------------------------------------------------------------------------------------------------------
| | Aug | Sep | Oct | Nov | Dec | Jan | Feb |
|---------------------------------+----------+----------+----------+----------+----------+----------+----------|
|Dollars Spent | $1,234| $2,234| $1,334| $1,244| $1,235| $6,234| $7,234|
|---------------------------------+----------+----------+----------+----------+----------+----------+----------|
|Months active | 12| 13| 14| 15| 16| 17| 18|
|---------------------------------+----------+----------+----------+----------+----------+----------+----------|
|Numbers of Transaction | 123| 123| 113| 123| 133| 143| 123|
|---------------------------------+----------+----------+----------+----------+----------+----------+----------|
|Average Percent | 8.1% | 7.7% | 6.6% | 5.5% | 8.4% | 3.3% | 8.2% |
----------------------------------------------------------------------------------------------------------------[/pre]
hope it still shows the required result

peterC
p.s.
weighting the "average_percentage" data would be possible, too
If you are that interested, look it up