BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
radha009
Quartz | Level 8

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     
%     
      
1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

6 REPLIES 6
ChrisNZ
Tourmaline | Level 20
Are you creating a table or a report? What have you tried?
radha009
Quartz | Level 8

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

Astounding
PROC Star

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&...

 

SuryaKiran
Meteorite | Level 14

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
radha009
Quartz | Level 8

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

 

SuryaKiran
Meteorite | Level 14

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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