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

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
PGStats
Opal | Level 21

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
jeka1212
Obsidian | Level 7

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;

 

PGStats
Opal | Level 21

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
jeka1212
Obsidian | Level 7
Many thanks PGStats. I am so grateful with your help. Many thanks also for you time.
jeka1212
Obsidian | Level 7

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)

 

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
  • 5 replies
  • 834 views
  • 0 likes
  • 2 in conversation