Proc Transpose Help

[buechler66]

Hi.  I'm having trouble with Proc Transpose.  My data currently looks like figure 1, but I need it to instead look like figure 2 with one row per Rule_NM and with ad_dt being columns along the top.  Can this be done?  I'm getting some undesireable results using this:

proc transpose data=FinalData
out=FinalData_2
name=ad_dt;
run;

                                            The SAS System                          08:52 Friday, April 8, 2016 204

                                                                                 
                                                                                
                        Obs    RULE_NM                                 Rule    Count        AD_DT

                          1    ACTUAL DELIVERY DATE MISSING IN IV         1        80       TOTAL
                          2    ACTUAL DELIVERY DATE MISSING IN IV         1         5       03/14/2016
                          3    ACTUAL DELIVERY DATE MISSING IN IV         1         3       03/15/2016
                          4    ACTUAL DELIVERY DATE MISSING IN IV         1        14       03/16/2016
                          5    ACTUAL DELIVERY DATE MISSING IN IV         1        13       03/17/2016
                          6    ACTUAL DELIVERY DATE MISSING IN IV         1        20       03/18/2016
                          7    ACTUAL DELIVERY DATE MISSING IN IV         1        19       03/19/2016
                          8    ACTUAL DELIVERY DATE MISSING IN IV         1         4       03/22/2016
                          9    ACTUAL DELIVERY DATE MISSING IN IV         1         2       03/25/2016
                         26    ACTUAL DELIVERY DATE LATER IN IV         1.5         8       04/02/2016
                         27    ACTUAL DELIVERY DATE MISMATCH              2       295       TOTAL
                         28    ACTUAL DELIVERY DATE MISMATCH              2         1       03/24/2016
                         29    ACTUAL DELIVERY DATE MISMATCH              2         1       03/25/2016
                         30    ACTUAL DELIVERY DATE MISMATCH              2       293       NULL

RULE_ORDER	RULE_NM	                        TOTAL	'04-APR-16'	'02-APR-16'	'01-APR-16'	'31-MAR-16'	'30-MAR-16'	'29-MAR-16'	'28-MAR-16'	'26-MAR-16'	'25-MAR-16'	'24-MAR-16'	'23-MAR-16'	'22-MAR-16'	'21-MAR-16'	'19-MAR-16'	'18-MAR-16'	'17-MAR-16'	'16-MAR-16'	'15-MAR-16'	'14-MAR-16'	'09-MAR-16'	NULLS
2	        ACTUAL DELIVERY DATE MISMATCH	295									                                                                1	        1											                                                                                        293

[Reeza]

What's your final destination? It seems like this may be better done via a proc tabulate or proc report?

[ballardw]

The inclusion of Total and Null values in a date field indicate that the data is malformed for many purposes

It would likely be possible to go back to "raw" dat and generate that output from single record values and count them than to massage data that has summaries included as it appears.

[buechler66 · Accepted solution]

Got it.

proc transpose data=FinalData out=FinalData_2;
by rule_nm rule_order;
id ad_dt_char;
var n;
run;