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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

17 REPLIES 17
sasecn
Quartz | Level 8

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
;
novinosrin
Tourmaline | Level 20
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;
sasecn
Quartz | Level 8

Great! That's what I want.

 

Thank you very much!

Miracle
Barite | Level 11

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;

 

sasecn
Quartz | Level 8

Hello Wong:

 

The code works well. Thanks!

sasecn
Quartz | Level 8

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.
 

novinosrin
Tourmaline | Level 20

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 @Miracle '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.

sasecn
Quartz | Level 8

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!

novinosrin
Tourmaline | Level 20

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. 

sasecn
Quartz | Level 8

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.

 

 

Ksharp
Super User
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;
sasecn
Quartz | Level 8

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

Ksharp
Super User

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;

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
  • 17 replies
  • 1656 views
  • 6 likes
  • 4 in conversation