topic Re: Automation Cross Tabulation in SAS Programming

Automation Cross Tabulation

jeka1212 — Sun, 04 Mar 2018 05:22:51 GMT

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?

Re: Automation Cross Tabulation

PGStats — Sun, 04 Mar 2018 06:05:22 GMT

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.

Re: Automation Cross Tabulation

jeka1212 — Sun, 04 Mar 2018 15:26:12 GMT

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;

Re: Automation Cross Tabulation

PGStats — Mon, 05 Mar 2018 03:22:24 GMT

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;

Re: Automation Cross Tabulation

jeka1212 — Mon, 05 Mar 2018 04:11:17 GMT

Many thanks PGStats. I am so grateful with your help. Many thanks also for you time.

Re: Automation Cross Tabulation

jeka1212 — Wed, 07 Mar 2018 00:03:13 GMT

Hi PGStats and all,

I need to modify the codes above to create a combined binary variable beta1_beta2 with y1 and y2 this time. I was wondering if you can advise me how to modify the previous SAS codes please?

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)