Automation Cross Tabulation

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Automation Cross Tabulation

Hi all,

 

I have continue variable y1. I need to create a binary variable from it using the first observation of y1. Then tabulate the created binary variable with the variable d in the attached dataset. 

 

I need to re[eat this process for all observations of  of y1. 

 

I have written SAS codes below and was wondering if you can help to modify it so that I have different cross tabulate tables for each observation of y1.

 

here is my SAS codes:   

 


** I have dataset "wiedat2b" and ordering it by variable order;
data want;
set wiedat2b;
by y1;
retain order 0;
if first.y1 then order=order+1;
run;

 

** then create binary variable beta and tabulate the results;

 

DATA want;
set want;
** Need Create a binary variable;
if y1 <=2.4 then beta2=0; * 2.4 is the FIRST observation of y1;
else beta1=1;
proc freq data = want order = formatted;
tables d * beta / nocol nopercent;
run;

** then create binary variable 2 and tabuate the results;
DATA want;
set want;
** Need Create a binary variable;
if y1 <=3.4 then beta2=0; * 3.4 is the SECOND obsrvation of y1;
else beta2=1;
proc freq data = want order = formatted;
tables d * y11 / nocol nopercent;
run;

 

**HOW TO REPEAT THIS PROCESS FOR EACH OBSERVATION OF y1 and tabulate the results?

 


Accepted Solutions
Solution
‎03-04-2018 11:08 PM
Esteemed Advisor
Posts: 5,540

Re: Automation Cross Tabulation

Had to guess what variable d actually is in your data... You seem to need something like this:

 

data have;
infile "&sasforum\datasets\wiedat2b.csv" firstobs=2 dsd;
input y1 y2 kyphosis;
run;

proc sql;
create table temp as
select distinct
    a.y1 as compare_y1,
    b.*,
    a.y1 < b.y1 as beta
from have as a, have as b
order by compare_y1, y1;
quit;

proc freq data=temp noprint;
by compare_y1;
table beta*kyphosis / chisq;
output out=myChisq chisq;
run;

replace chisq with whatever table statistic you want.

 

PG

View solution in original post


All Replies
Solution
‎03-04-2018 11:08 PM
Esteemed Advisor
Posts: 5,540

Re: Automation Cross Tabulation

Had to guess what variable d actually is in your data... You seem to need something like this:

 

data have;
infile "&sasforum\datasets\wiedat2b.csv" firstobs=2 dsd;
input y1 y2 kyphosis;
run;

proc sql;
create table temp as
select distinct
    a.y1 as compare_y1,
    b.*,
    a.y1 < b.y1 as beta
from have as a, have as b
order by compare_y1, y1;
quit;

proc freq data=temp noprint;
by compare_y1;
table beta*kyphosis / chisq;
output out=myChisq chisq;
run;

replace chisq with whatever table statistic you want.

 

PG
Contributor
Posts: 27

Re: Automation Cross Tabulation

Many Thanks. I have what i wanted using the modified codes  below. I have attached a sample of the output from SAS  and was wondering if you can help to select some results in a transpose table. Many thanks in advance

 

data have;
infile "&sasforum\datasets\wiedat2b.csv" firstobs=2 dsd;
input y1 y2 kyphosis;
run;

proc sql;
create table temp as
select distinct
    a.y1 as compare_y1,
    b.*,
    a.y1 < b.y1 as beta
from have as a, have as b
order by compare_y1, y1;
quit;

proc sort data=temp;
         by descending beta descending kyphosis;
         run;

proc freq data = temp order=data noprint ;
by compare_y1;
            tables  beta*kyphosis / out=FreqCount OUTPCT sparse ;
			*output out=mnocol;
run;

 

Esteemed Advisor
Posts: 5,540

Re: Automation Cross Tabulation

Try something like this:

 


data freqTable;
merge 
    freqCount (
        keep=compare_y1 beta kyphosis count 
        rename=(count=falpos) 
        where=(beta=0 and kyphosis=1))
    freqCount (
        keep=compare_y1 beta kyphosis count 
        rename=(count=falneg) 
        where=(beta=1 and kyphosis=0))
    freqCount (
        keep=compare_y1 beta kyphosis count pct_row pct_col
        rename=(count=neg pct_row=npv pct_col=specif) 
        where=(beta=0 and kyphosis=0))
    freqCount (
        keep=compare_y1 beta kyphosis count pct_row pct_col
        rename=(count=pos pct_row=ppv pct_col=sensit) 
        where=(beta=1 and kyphosis=1));
by compare_y1;
run;

proc print data=freqTable noobs; 
var compare_y1 pos neg falpos falneg sensit specif npv ppv; 
run;
PG
Contributor
Posts: 27

Re: Automation Cross Tabulation

Many thanks PGStats. I am so grateful with your help. Many thanks also for you time.
Contributor
Posts: 27

Re: Automation Cross Tabulation

Hi 

 

data have;
infile "&sasforum\datasets\wiedat2b.csv" firstobs=2 dsd;
input y1 y2 kyphosis;
run;

DATA have;
set have;

********Creating the first binary variable beta1 with y1=28;
if y1 <= 28 then beta1=0;
else beta1=1; 

**Creating the first binary variable beta2 with y2=13.3;
if y2 <=13.3 then beta2=0;
else beta2=1;

********Creating the first  combined binary variable beta1_beta2 with the pair (y1=28,y2=13.3);

if beta1=1 & beta2=1 then beta1_beta2=1;
if beta1=1 & beta2=0 then beta1_beta2=1;
if beta1=0 & beta2=1 then beta1_beta2=1;
if beta1=0 & beta2=0 then beta1_beta2=0;
run;

proc freq data = have order=data noprint ;
            tables  beta1_beta2*kyphosis / out=FreqCount OUTPCT sparse ;
		*output out=mnocol;
run;

*THEN REPEAT  FOR:  y1=28, y2=11.1, 12.6, ect...; 

***Do the same for the second paire y1=28,y2=11.1;
***Do the same for the second paire y1=28,y2=12.6;
**........;
***Do the same for the last (141) paire y1=28,y2=14.2;


**THEN REPEAT FOR: y1=16.7, y2=11.1, 12.6, ect...; 

***Do the same for the second paire y1=16.7,y2=11.1;
***Do the same for the second paire y1=16.7,y2=12.6;
**........;
***Do the same for the last (141) paire y1=16.7,y2=14.2;

****HOW TO REPEAT THIS FROCESS FOR ALL 19881 COMBINATION IN THE DATASET (141 X 141 POSSIBLE COMBINATIONS)

 

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 210 views
  • 0 likes
  • 2 in conversation