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
Got it.
proc transpose data=FinalData out=FinalData_2;
by rule_nm rule_order;
id ad_dt_char;
var n;
run;
What's your final destination? It seems like this may be better done via a proc tabulate or proc report?
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.
Got it.
proc transpose data=FinalData out=FinalData_2;
by rule_nm rule_order;
id ad_dt_char;
var n;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.