Solved
Contributor
Posts: 67

# subtract the values in a dataset

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 %

Accepted Solutions
Solution
‎04-24-2018 09:40 AM
Valued Guide
Posts: 559

## Re: subtract the values in a dataset

[ Edited ]

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,
Suryakiran

All Replies
PROC Star
Posts: 2,316

## Re: subtract the values in a dataset

Are you creating a table or a report? What have you tried?
Contributor
Posts: 67

## Re: subtract the values in a dataset

add the diff and % to the existing table at difference row and % row.

Super User
Posts: 6,632

## Re: subtract the values in a dataset

[ Edited ]

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:

http://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n10t6w4op574ttn11yk68dottp7f.htm&...

Solution
‎04-24-2018 09:40 AM
Valued Guide
Posts: 559

## Re: subtract the values in a dataset

[ Edited ]

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,
Suryakiran
Contributor
Posts: 67

## Re: subtract the values in a dataset

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.

Valued Guide
Posts: 559

## Re: subtract the values in a dataset

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.

Thanks,
Suryakiran
☑ This topic is solved.