topic Calculate Difference and Percentage of difference in SAS Programming

Calculate Difference and Percentage of difference

subrat1 — Wed, 14 Nov 2018 09:39:03 GMT

HI I have an input table like below:

Week	Record_Count	Total Distinct Session ID	Total Distinct Submission Number	Total Distinct POLICY_NO	Total Distinct DIVISION_NO	Total Distinct Effective Date	Sum_TOT_TECH_PREM	Sum_TOT_TARGET_PREM
12NOV2018	416	321	295	46	6	125	142348899.8	133862781.3
05NOV2018	316	220	500	30	10	100	142312000.8	142312000.8

I want to Calculate Difference and Percentage of difference, and my output table should look like :

Week	Record_Count	Total Distinct Session ID	Total Distinct Submission Number	Total Distinct POLICY_NO	Total Distinct DIVISION_NO	Total Distinct Effective Date	Sum_TOT_TECH_PREM	Sum_TOT_TARGET_PREM
12NOV2018	416	321	295	46	6	125	142348899.8	133862781.3
05NOV2018	316	220	500	30	10	100	142312000.8	142312000.8
Diffrence	100	101	-205	16	-4	25	36899	-8449219.49
Percentage	24.0%	31.5%	-69.5%	34.8%	-66.7%	20.0%	0.0%	-6.3%

Re: Calculate Difference and Percentage of difference

RW9 — Wed, 14 Nov 2018 09:49:06 GMT

Post test data in the form of a datastep!!

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

At an untested guess:

proc sql;
  create table want as
  select *
  from   have
  union all
  select "Difference" as week,
         a.record_count-b.record_count as record_count,
         a.tds_id-b.tds_id as tds_id,
   ...
  from   (select * from have where week="12NOV2018") a
  full join (select * from have where week="05NOV2018") b
  on     1=1
  union all
  select "Percentage" as week,
         (a.record_count-b.record_count/a.record_count)*100 as record_count,
         (a.tds_id-b.tds_id/a.tds_id)*100 as tds_id,
   ...
  from   (select * from have where week="12NOV2018") a
  full join (select * from have where week="05NOV2018") b
  on     1=1;
quit;

Re: Calculate Difference and Percentage of difference

DanielLangley — Wed, 14 Nov 2018 13:45:54 GMT

This is going to be difficult to replicate exactly as you wish to have different formats for the same columns. This does almost what you want.

data have;
infile datalines dsd delimiter=' ';
input week Record_Count
Total_Distinct_Session_ID
Total_Distinct_Submission_Number
Total_Distinct_POLICY_NO
Total_Distinct_DIVISION_NO
Total_Distinct_Effective_Date
Sum_TOT_TECH_PREM
Sum_TOT_TARGET_PREM;
attrib week informat = date9. format = date9.;

cards;
12NOV2018 416 321 295 46 6 125 142348899.8 133862781.3
05NOV2018 316 220 500 30 10 100 142312000.8 142312000.8
;
run;

/*Transpose the Data so it is easier to manipulate*/
PROC TRANSPOSE DATA=WORK.Have
	OUT=WORK.HaveTransposed(LABEL="Transposed WORK.HAVE")
	NAME=Source
	PREFIX=Week
	LABEL=Label
;
	ID week;
	VAR _NUMERIC_;
RUN;

data work.haveManipulated;
set work.HaveTransposed;
array Week[2] Week:;

	Difference = Week[1] - Week[2];
	Percentage = round(  100*Divide(Difference,Week[1])  ,0.1);

run;

PROC TRANSPOSE DATA=work.haveManipulated
	OUT=work.want(Drop = Week)
	NAME=Source
	LABEL=Label
;
	ID Source;
	VAR _Numeric_;

RUN;

Re: Calculate Difference and Percentage of difference

Cynthia_sas — Wed, 14 Nov 2018 15:11:55 GMT

Hi:
If you were doing this in PROC REPORT or PROC TABULATE, both of those procedures would allow differing formats in the same column (where you show percentage in the row, but the other columns do not use a % sign).

PROC TABULATE won't calculate difference, however, the way you show, so I'd either pre-calculate the difference row or I'd use PROC REPORT. Even if I pre-calculated the row, I'd still use PROC REPORT to get the %sign for the Percentage row.
Cynthia