Solved
Contributor
Posts: 27

# 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
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

All Replies
Solution
‎03-04-2018 11:08 PM
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;

```

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.