Transpose limitation

[SanjayM]

Hello

I have a dataset

ID----TransactionDate------OrdType-----Quantity-------Value
1-----01/01/2007--------------A--------------100--------------1000
1-----02/01/2007--------------A--------------200--------------2000
1-----01/02/2007--------------A--------------300--------------3000
1-----01/02/2007--------------A--------------400--------------4000
1-----01/03/2007--------------A--------------500--------------5000
1-----01/03/2007--------------A--------------600--------------6000

1-----01/01/2007--------------B--------------100--------------1001
1-----02/01/2007--------------B--------------200--------------2001
1-----01/02/2007--------------B--------------300--------------3001
1-----01/02/2007--------------B--------------400--------------4001
1-----01/03/2007--------------B--------------500--------------5001
1-----01/03/2007--------------B--------------600--------------6001

I want to show an output from EG using transpose as shown below


OrderType-------------------January--------------------February---------------Total
----------------------------Quantity------Value-------Quantity------Value-----


Pls help. Ignore the lines it was used just to show output

Query is same as posted in
http://support.sas.com/forums/thread.jspa?messageID=7113ᯉ

Can we do it using the Transpose task of EG. It is possible using one cloumn like quantity but not possible if there are multiple columns.

[Cynthia_sas]

Do you want a REPORT or a DATA set? If you want a REPORT, then I would recommend the use of PROC TABULATE. If you want a DATA set, then I would recommend either PROC TRANSPOSE or a DATA step program. It looks to me like you want ALL the rows for the days in JAN to be added and ALL the rows for the days in FEB to be added up.

Examine the file SASHELP.PRDSALE...it has a lot of DETAIL records for Actual and Predicted Sales. I can produce this REPORT table using PROC TABULATE (or the Summary Table task in EG):
[pre]
TABULATE Summary Report

+---------+-----------------------------------------------------+-----------------+
| | Month | |
| +-----------------+-----------------+-----------------+ |
| | Jan | Feb | Mar | Total |
| +--------+--------+--------+--------+--------+--------+--------+--------+
| | Actual |Predict | Actual |Predict | Actual |Predict | Actual |Predict |
+---------+--------+--------+--------+--------+--------+--------+--------+--------+
|Product | | | | | | | | |
|type | | | | | | | | |
+---------+ | | | | | | | |
|FURNITURE| 23,843| 28,445| 23,348| 24,397| 22,585| 23,560| 69,776| 76,402|
+---------+--------+--------+--------+--------+--------+--------+--------+--------+
|OFFICE | 39,674| 36,653| 33,949| 34,263| 35,634| 38,883| 109,257| 109,799|
+---------+--------+--------+--------+--------+--------+--------+--------+--------+
|Total | 63,517| 65,098| 57,297| 58,660| 58,219| 62,443| 179,033| 186,201|
+---------+--------+--------+--------+--------+--------+--------+--------+--------+

[/pre]

Where I have MONTH, you would have your date variable (formatted with MONNAME3. probably).
Where I have ACTUAL, you would have Quantity.
Where I have PREDICT, you would have Value.
Where I have PRODTYPE, you would have Order Type.
I put TOTAL at the bottom of all the ROW and TOTAL at the end of all the COLUMNS -- you could put it at one place or the other or leave it at both.
What does the number 23,843 represent?? It is the summing up of ACTUAL for all the rows where PRODTYPE=FURNITURE and month (formatted with MONNAME3.) = JAN.

You CAN produce this kind of REPORT using the Summary Table task in Enterprise Guide. Or you could use PROC TABULATE code.

For more help with the difference between PROC TRANSPOSE to create a data set and the Summary Table task to create a report with the months going across the columns, you might consider contacting Tech Support. The PROC TABULATE code that produced the above REPORT is shown below (I used the WHERE clause as a convenience to get only 3 months of data.).

cynthia
[pre]

proc tabulate data=sashelp.prdsale format=comma8.;
title 'TABULATE Summary Report';
where quarter = 1;
var actual predict;
class month prodtype;
table prodtype all,
month*(actual predict) all*(actual predict)
/rts=11;
keylabel sum=' '
all='Total';
label actual = 'Actual'
predict = 'Predict';
run;
[/pre]

[deleted_user]

Hi

Thanx for the solution.
But how to supress dot(.) values in the numeric field .
for example:
In your report output which you mentioned,
If suppose there is no actual or predict value of product type furniture in the month jan then it will display as '.' (dot) .
Can you tell me any option so taht i can substitute 0 instead of '.'

Sunil

[Cynthia_sas]

Hi:
Look in the documentation for the SAS System Option named MISSING. These are only some possibilities for the MISSING OPTION:

missing=0
missing=' '
missing = 'z'
missing = '0'

To find a list of the SAS System Options and see the specific syntax of the SAS OPTIONS statement, look in the documentation for the topic:
SAS System Options by Category

PROC TABULATE has an alternative way to represent missing values for calculated numeric values. For example, what if your store did not open until February -- due to unseen problems. You might have a predicted amount for January sales, but if the store was not open, there would not be any value OTHER than missing possible for actual sales. In that case, you might want to show "N/A" or "not applicable" or "no sales" in the calculated cell for January. To learn more about using this TABULATE-specific method, you can investigate the "MISSTEXT" option for PROC TABULATE.

cynthia