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% |
Post test data in the form of a datastep!!
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;
Post test data in the form of a datastep!!
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.