Cross Tabulation of Two variables

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Cross Tabulation of Two variables

Hi all,

 

I need  to create a combined binary variable beta1_beta2  with y1 and y2 and use the existing binary variable “kyphosis”  with proc beta1_beta2  *kyphosis.  I was wondering if you can advise me how to modify the SAS code below to repeat the process for all pair (y1,y2) in the dataset? 

 

Thanks,

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)

Accepted Solutions
Solution
‎03-10-2018 10:32 AM
Contributor
Posts: 27

Re: Cross Tabulation of Two variables

Many thanks ChrisNZ. The codes did help. 

 

I was wondering if you can help to append  each results in 1 big dataset please?  I have attached an example.

data HAVE;
  input Y1 Y2 KYPHOSIS;
cards;
28 11.1 100
28 13.3 200
16 16.1 300
16 13.3 400
run;

data _null_;
  set HAVE(keep=Y1) nobs=NOBS;
  do I=1 to NOBS;
    J+1;
    set HAVE(keep=Y2) point=I;
    call execute('data TEMP; set HAVE;');
    call execute(catt('BETA1_BETA2=(Y1<=',Y1,')|(Y2<=',Y2,');'));
    call execute('proc freq data=TEMP order=data noprint;');
    call execute('tables BETA1_BETA2*KYPHOSIS');
    call execute(catt('/out=OUTFREQ',J,'(label="Y1=',Y1,' Y2=',Y2,'") outpct sparse;run;'));
  end;
run;

View solution in original post


All Replies
Highlighted
PROC Star
Posts: 2,370

Re: Cross Tabulation of Two variables

[ Edited ]

Liek this?

data HAVE;
  input Y1 Y2 KYPHOSIS;
cards;
28 11.1 100
28 13.3 200
16 16.1 300
16 13.3 400
run;

data _null_;
  set HAVE(keep=Y1) nobs=NOBS;
  do I=1 to NOBS;
    J+1;
    set HAVE(keep=Y2) point=I;
    call execute('data TEMP; set HAVE;');
    call execute(catt('BETA1_BETA2=(Y1<=',Y1,')|(Y2<=',Y2,');'));
    call execute('proc freq data=TEMP order=data noprint;');
    call execute('tables BETA1_BETA2*KYPHOSIS');
    call execute(catt('/out=OUTFREQ',J,'(label="Y1=',Y1,' Y2=',Y2,'") outpct sparse;run;'));
  end;
run;



 

 

Solution
‎03-10-2018 10:32 AM
Contributor
Posts: 27

Re: Cross Tabulation of Two variables

Many thanks ChrisNZ. The codes did help. 

 

I was wondering if you can help to append  each results in 1 big dataset please?  I have attached an example.

data HAVE;
  input Y1 Y2 KYPHOSIS;
cards;
28 11.1 100
28 13.3 200
16 16.1 300
16 13.3 400
run;

data _null_;
  set HAVE(keep=Y1) nobs=NOBS;
  do I=1 to NOBS;
    J+1;
    set HAVE(keep=Y2) point=I;
    call execute('data TEMP; set HAVE;');
    call execute(catt('BETA1_BETA2=(Y1<=',Y1,')|(Y2<=',Y2,');'));
    call execute('proc freq data=TEMP order=data noprint;');
    call execute('tables BETA1_BETA2*KYPHOSIS');
    call execute(catt('/out=OUTFREQ',J,'(label="Y1=',Y1,' Y2=',Y2,'") outpct sparse;run;'));
  end;
run;
PROC Star
Posts: 2,370

Re: Cross Tabulation of Two variables

Microsoft documents are best avoided.

Many people will not touch them.

Just add this:

    call execute(catt('proc append base=BASE append=OUTFREQ',J,';run;'));

 

Contributor
Posts: 27

Re: Cross Tabulation of Two variables

OK many thanks.

 

I am having the error message below:

 

 

NOTE: Line generated by the CALL EXECUTE routine.
96 + proc append base=BASE append=OUTFREQ16;run;
------
22
76

ERROR 22-322: Syntax error, expecting one of the following: ;, (, APPENDVER, APPENDVERSION,
BASE, CREATE, DATA, FORCE, GETSORT, NEW, NOWARN, OUT.

ERROR 76-322: Syntax error, statement will be ignored.

 

 

Basically each paire of (y1,y1) has "OUTFREQ" output like this:

 

Beta1_beta2

kyphosis

COUNT

PERCENT

PCT_ROW

PCT_COL

0

1

1

0.70922

100

1.111111

0

0

0

0

0

0

1

1

89

63.12057

63.57143

98.88889

1

0

51

36.17021

36.42857

100

 

I need to append it like this: 

 

Y1

Y2

perctCount POS

perct count

NEG

FALPOS

FALNEG

SENSIT

SPECIF

NPV

PPV

16

13.3

89

0

1

51

98.89

0

0

63.57143

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any help? 

PROC Star
Posts: 2,370

Re: Cross Tabulation of Two variables

[ Edited ]

>proc append base=BASE append=OUTFREQ16;run;

 

My bad, end of Friday evening error. data= not append=.

 

Have even bothered looking at the syntax for proc append? You have to help yourself too.

 

As for reformatting the dataset, you can add an extra step after table BASE is created.

 

Contributor
Posts: 27

Re: Cross Tabulation of Two variables

I corrected the error after I posted the question. Thanks again

Still working on reformatting now
PROC Star
Posts: 2,370

Re: Cross Tabulation of Two variables

> I corrected the error after I posted the question. 

 

You should have edited your question then, to avoid my spending time giving you an answer you already had.

 

Contributor
Posts: 27

Re: Cross Tabulation of Two variables

Sorry
PROC Star
Posts: 2,370

Re: Cross Tabulation of Two variables

No worries. For next time. Smiley Happy

Contributor
Posts: 27

Re: Cross Tabulation of Two variables

is your book (High-Performance SAS Coding) good for beginners in programming  like us?

PROC Star
Posts: 2,370

This book is not targeted at beginners. The SAS language...

[ Edited ]

This book is not targeted at beginners. The SAS language itself is not taught. But as you learn it, the book shows how to use the language's features efficiently and explains what happens behind the scene. There are many ways to perform a given task with SAS. Some are much more efficient than others, 

Contributor
Posts: 27

Re: This book is not targeted at beginners. The SAS language...

OK good to know. Will order one as soon as possible
PROC Star
Posts: 2,370

Re: This book is not targeted at beginners. The SAS language...

Thank you. I hope you find it useful.

Super User
Posts: 13,583

Re: Cross Tabulation of Two variables

 

beta1 = y1>28;
beta2 = y2>13.3;

is another way to assign the beta variables.

 

Do you really want to assign 0 if y1 or y2 are actually missing though?

 

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;

(though else would help on the 2nd 3rd and 4th If)

 

is the same as

beta1_beta2= beta1 or beta2;

which will execute a tad faster.

 

 

Which will matter on doing 19881 iterations.

 

You better have something that saves your Proc Freq output to a unique name each time or each iteration will overwrite the previous

☑ This topic is solved.

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

Discussion stats
  • 15 replies
  • 277 views
  • 1 like
  • 3 in conversation