How to subtract and get the % from the below table in SAS. - Thank you
difference = mar.test1-feb.test1
% =mar.test1-feb.test1/feb.test1
month | test1 | test2 | test3 | test4 | test5 |
Jan | 357 | 240 | 421 | 231 | 174 |
Feb | 358 | 305 | 557 | 182 | 359 |
Mar | 277 | 245 | 568 | 279 | 329 |
difference | |||||
% | |||||
Do you really have only 3 observations? You can use LAG() and DIF() function to get your values in one single record, then later you need to vertically join on conditions.
data have;
infile datalines dlm=" " dsd;
Input month :$8. test1-test5 :8.;
datalines;
Jan 357 240 421 231 174
Feb 358 305 557 182 359
Mar 277 245 568 279 329
;
run;
data MOD ;
set have;
array diff{5} diff1-diff5;
array per{5} per1-per5;
array test{5} test1-test5;
do i=1 to 5;
diff{i}=DIF(test{i});
per{i}=DIF(test{i})/LAG(test{i});
end;
run;
proc sql;
create table want(drop=order) as
select monotonic() as order,month,test1,test2,test3,test4,test5
from MOD
union
select 99 as order,
"Difference" as Month,
diff1 as test1,
diff2 as test2,
diff3 as test3,
diff4 as test4,
diff5 as test5
from MOD where month="Mar"
Union
select 9999 as order,
"Percent" as Month,
per1 as test1,
per2 as test2,
per3 as test3,
per4 as test4,
per5 as test5
from MOD where month="Mar"
order by order
;
quit;
Also check out similar post here: https://communities.sas.com/t5/Base-SAS-Programming/Compare-two-table-and-then-find-difference/td-p/...
add the diff and % to the existing table at difference row and % row.
Here's something you can play with to get started:
data want;
set have;
pct1 = dif(test1) / lag(test1);
pct2 = dif(test2) / lag(test2);
pct3 = dif(test3) / lag(test3);
pct4 = dif(test4) / lag(test4);
pct5 = dif(test5) / lag(test5);
run;
You can find documentation on the DIF function here:
Do you really have only 3 observations? You can use LAG() and DIF() function to get your values in one single record, then later you need to vertically join on conditions.
data have;
infile datalines dlm=" " dsd;
Input month :$8. test1-test5 :8.;
datalines;
Jan 357 240 421 231 174
Feb 358 305 557 182 359
Mar 277 245 568 279 329
;
run;
data MOD ;
set have;
array diff{5} diff1-diff5;
array per{5} per1-per5;
array test{5} test1-test5;
do i=1 to 5;
diff{i}=DIF(test{i});
per{i}=DIF(test{i})/LAG(test{i});
end;
run;
proc sql;
create table want(drop=order) as
select monotonic() as order,month,test1,test2,test3,test4,test5
from MOD
union
select 99 as order,
"Difference" as Month,
diff1 as test1,
diff2 as test2,
diff3 as test3,
diff4 as test4,
diff5 as test5
from MOD where month="Mar"
Union
select 9999 as order,
"Percent" as Month,
per1 as test1,
per2 as test2,
per3 as test3,
per4 as test4,
per5 as test5
from MOD where month="Mar"
order by order
;
quit;
Also check out similar post here: https://communities.sas.com/t5/Base-SAS-Programming/Compare-two-table-and-then-find-difference/td-p/...
Thanks a lot for the solution.it worked perfect. I am trying to display the percentage values in format 6%, i added the format statement in the dataset Mod, but in Sql the format is not displaying
format per1-per8 percentn8.
Format cannot be applied to only one specific row, you can convert them to Char variables instead of numeric values to display how you need.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.