DATA Step, Macro, Functions and more

subtract the values in a dataset

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

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

 

monthtest1test2test3test4test5
Jan357240421231174
Feb358305557182359
Mar277245568279329
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

View solution in original post


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

Posted in reply to SuryaKiran

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.

 

sss.jpg

 

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 129 views
  • 0 likes
  • 4 in conversation