topic Re: Pivot Data Proc Report vs Proc Transpose?? in SAS Programming

Pivot Data Proc Report vs Proc Transpose??

Q1983 — Tue, 06 Apr 2021 01:00:34 GMT

data have1;
format bi_date date9.;
infile datalines;
input ln_no $4.
days_past1
bi_date date9.
tot_cnt

;
return;
datalines;
1111 12 31Mar2020 1
2122 12 31Mar2020 1
1244 12 31Mar2020 1
3333 44 31Mar2020 1
2222 30 31Mar2020 1

;run;

data have2;
format bi_date date9.;
infile datalines;
input ln_no $4.
days_past2
bi_date date9.
tot_cnt

;
return;
datalines;
1111 22 30Apr2020 1
2122 32 30Apr2020 1
1244 31 30Apr2020 1
3333 30 30Apr2020 1
2222 13 30Apr2020 1

;run;

proc sort data=have1;by ln_no;run;
proc sort data=have2;by ln_no;run;

data merge_all;
merge have1 have2;
by ln_no;
run;

/***********************************/

produces the following

Category	bi_date1	ln_no	days_past1	tot_cnt	bi_date2	days_past2
Current	31Mar2020	1111	12	1	30Apr2020	22
Curr_Past_Due	31Mar2020	1244	12	1	30Apr2020	31
Curr_Past_Due	31Mar2020	2122	12	1	30Apr2020	32
Current	31Mar2020	2222	30	1	30Apr2020	13
Past_to_Current	31Mar2020	3333	44	1	30Apr2020	30

I attempting to do a pivot report however proc transpose only captures the first instance of date statistics. I am thinking a proc report may do the trick but wanted input. Here is the desired output

Category	31-Mar-20	30-Apr-20	Pct_Chg
Current	1	1	0%
Curr_Past_Due	1	1	0%
Past_to_Current	1	0	50%

Grand Total

Re: Pivot Data Proc Report vs Proc Transpose??

Kurt_Bremser — Tue, 06 Apr 2021 07:10:57 GMT

What is your formula for calculating the Category values?

Re: Pivot Data Proc Report vs Proc Transpose??

Q1983 — Tue, 06 Apr 2021 11:48:20 GMT

Here is the last part with the category formula
data merge_all;
length Category $25.;
merge have1 have2;
by ln_no;
if days_past1 <=30 and days_past2 <=30 then Category='Current';
if days_past1 <=30 and days_past2 > 30 then Category='Curr_Past_Due';
if days_past1 > 30 and days_past2 <=30 then Category='Past_to_Current';

run;

Re: Pivot Data Proc Report vs Proc Transpose??

Tom — Tue, 06 Apr 2021 12:25:51 GMT

Please share the PROC REPORT code.

Re: Pivot Data Proc Report vs Proc Transpose??

Kurt_Bremser — Tue, 06 Apr 2021 13:25:59 GMT

That's highly illogical; since the category is derived from BOTH dates, there cannot be a change from one date to the other, so the report as posted makes no sense and is factually wrong.