DATA Step, Macro, Functions and more

Percentage calculation: calculate percentage changes in variable values

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Percentage calculation: calculate percentage changes in variable values

Hello:

I have a data set as below. I want to create a two-table that each cell has a value as percentage of changes in values. E.G. for variable "x1", it has values: a, b, c, d (in exact data, it has more values. so using loop for all values are desired). I want to get the percent of "a" changing to "b", "c", "d", and percent of remaining in "a" (based on the values of variable "x2").

 

The data:

data test;
input x1 $1 x2 $1;
cards;
a a
a a
a b
a c
a d
b b
b a
b c
b d
c c
c c
c c
c a
d d
d a
d b
d b
;
run;

So my goal is to get a table like:

     a           b         c         d

a   2/5        1/5    1/5       1/5

b   1/4        1/4    1/4       1/4

c   1/4        3/4      0          0

d   1/4        2/4      0        1/4

 

Any help is great!

 

Thanks a lot!


Accepted Solutions
Solution
‎03-09-2018 09:53 PM
PROC Star
Posts: 1,357

Re: Percentage calculation: calculate percentage changes in variable values

[ Edited ]
data test;
input x1 $1. x2 :$1.;
cards;
a a
a a
a b
a c
a d
b b
b a
b c
b d
c c
c c
c c
c a
d d
d a
d b
d b
;
run;


proc sql;
create table t as
select x1,x2, count(x2)	as c
from test
group by x1,x2;
quit;
proc transpose data=t out=want(drop=_name_) ;
by x1;
var c;
id x2;
run;

data final_want;
set want;
array t(*) _numeric_;
k=sum(of t(*));
do _n_=1 to dim(t);
if missing(t(_n_)) then t(_n_)=0;
else t(_n_)=t(_n_)/k*100;
end;
drop k;
run;

View solution in original post


All Replies
Contributor
Posts: 27

Re: Percentage calculation: calculate percentage changes in variable values

Sorry, i think my data code is somehow getting problems, Please use the following:

data have;
   input x1 $ x2 $;
   datalines;
a a
a a
a b
a c
a d
b b
b a
b c
b d
c c
c c
c c
c a
d d
d a
d b
d b
;
Solution
‎03-09-2018 09:53 PM
PROC Star
Posts: 1,357

Re: Percentage calculation: calculate percentage changes in variable values

[ Edited ]
data test;
input x1 $1. x2 :$1.;
cards;
a a
a a
a b
a c
a d
b b
b a
b c
b d
c c
c c
c c
c a
d d
d a
d b
d b
;
run;


proc sql;
create table t as
select x1,x2, count(x2)	as c
from test
group by x1,x2;
quit;
proc transpose data=t out=want(drop=_name_) ;
by x1;
var c;
id x2;
run;

data final_want;
set want;
array t(*) _numeric_;
k=sum(of t(*));
do _n_=1 to dim(t);
if missing(t(_n_)) then t(_n_)=0;
else t(_n_)=t(_n_)/k*100;
end;
drop k;
run;
Contributor
Posts: 27

Re: Percentage calculation: calculate percentage changes in variable values

Posted in reply to novinosrin

Great! That's what I want.

 

Thank you very much!

Regular Contributor
Posts: 249

Re: Percentage calculation: calculate percentage changes in variable values

or using proc freq and proc transpose? Smiley Happy

proc freq data=have; 
	tables x1*x2 / noprint nofreq nocol nopercent sparse outpct out=temp(keep=x1 x2 PCT_ROW); 
run;
proc transpose data=temp out=want(drop=_Name_ _Label_); 
	by x1; 
	id x2; 
run;
proc print data=want noobs; run;

 

Contributor
Posts: 27

Re: Percentage calculation: calculate percentage changes in variable values

Hello Wong:

 

The code works well. Thanks!

Contributor
Posts: 27

Re: Percentage calculation: calculate percentage changes in variable values

Posted in reply to novinosrin

Hello 

 

 

proc sql;
create table t as
select x1,x2, count(x2)	as c
from test
group by x1,x2;

My data is not sorted.I also got an error later as: Data set MYDATA.T is not sorted in ascending sequence.

 

Does the code only run if the data is sorted?

 

Thanks.
 

PROC Star
Posts: 1,357

Re: Percentage calculation: calculate percentage changes in variable values

Does the code only run if the data is sorted?

 

No, Sql doesn't require for the dataset to be sorted. I am not sure why the code isn't working for your real data. However @wong 's code is rather robust as proc freq takes care of most of the work. I would suggest to try that as opposed to the longer process.

Contributor
Posts: 27

Re: Percentage calculation: calculate percentage changes in variable values

Posted in reply to novinosrin

Thank you for your reply. You are right that Wong's code works fine. I am also interested in learning sql process. Not sure why the code not working on my real data. But your code also teaches me something I need in the future. Thanks!

PROC Star
Posts: 1,357

Re: Percentage calculation: calculate percentage changes in variable values

Indeed that's why i am in this forum. The sql logic is too simple and aint a big deal. If it's not a matter of data security in business production environment, i would ask you to share more of your real so that I can debug and test from my side. Obviously, we can't do that against ethics. 

Contributor
Posts: 27

Re: Percentage calculation: calculate percentage changes in variable values

Posted in reply to novinosrin

Well, I cannot share the real data here. It is confidential. But it basically just like the test data. The only two variables needed are locations of a firm who may move to other locations or remain in original place in certain period. Also, it is not sorted. I didn't see anything wrong with the code. Just don't know why I got that error. I also notice that it creates a table "t" in the sql procedure. But the column "c" has only one value which is the number of observations.

 

 

Super User
Posts: 10,626

Re: Percentage calculation: calculate percentage changes in variable values

data test;
input x1 $ x2 $;
cards;
a a
a a
a b
a c
a d
b b
b a
b c
b d
c c
c c
c c
c a
d d
d a
d b
d b
;
run;
proc tabulate data=test;
class x1 x2;
table x1='',x2=''*rowpctn=''/misstext='0';
run;
Contributor
Posts: 27

Re: Percentage calculation: calculate percentage changes in variable values

Thanks for your reply. It works. What if I also want the frequency count in the result?

Super User
Posts: 10,626

Re: Percentage calculation: calculate percentage changes in variable values

OK. Easy for PROC TABULATE .

 

data test;
input x1 $ x2 $;
cards;
a a
a a
a b
a c
a d
b b
b a
b c
b d
c c
c c
c c
c a
d d
d a
d b
d b
;
run;
proc tabulate data=test;
class x1 x2;
table x1='',x2=''*(n='n' rowpctn='rowpctn%')/misstext='0';
run;
Contributor
Posts: 27

Re: Percentage calculation: calculate percentage changes in variable values

Thank you!

☑ This topic is solved.

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

Discussion stats
  • 17 replies
  • 226 views
  • 6 likes
  • 4 in conversation