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

Hi experts,

 

I am fairly new to SAS and I'm grateful for any input.

 

I would like to create a 2x2 table in which the number of rows are retrieved from multiple variables in multiple tables in order to calculate sensitivity and specificity. 

 

Specifically, I want to retreive the number of rows in three different columns in three different tables. The three counts reflect the number of records in database 1, database 2 and number of matches between the two databases. 

 

Ideally, I'd like SAS to calculate the missing values and end up with a table like this one: 

 

Database 2 YesNoTotal
Database 1   
Yes 700to be calculated 800
Noto be calculated to be calculatedto be calculated 
Total920  

 

However I am already stuck at retrieving the counts. I have tried with the following insert into statement (table3 has already been created) but it doesn't work.

 

proc sql;
	insert into table3 (count1, count2, match)
		select count (ID1) from table1,
		select count (ID2) from table2,
		select count (match) from table3;

 

Any help will be appreciated.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

Not really sure what the output table you show is designed to represent, but I can help you get the statistics. Something like this:

 

data one;
   do ID1=1 to 100 by 2;
		output;
	end;
run;
data two;
   do ID2=1 to 100 by 3;
		output;
	end;
run;

proc sql;
title 'Input Table Row Counts';
select 'One' as Table, count(*) as Count from One
  union all
select 'Two',count(*) from Two
;
title 'Table Comparison Counts';
select 
       sum (id1 and id2) as Match
		,sum (sum(missing(id1),missing(id2))) as NoMatch
		,count(ID1) as Rows1 'Rows in One'
		,count(ID2) as Rows2 'Rows in Two'
		,count(*)   as JoinRows 'Full Join Rows'
		,sum(count(ID1),count(ID2)) as Total 'All Rows'
	from one
		full join 
		  two
	on one.id1=two.id2
;
quit;

Produces this result:

Input Table Row Counts

 

Table Count
One 50
Two 34
 
Table Comparison Counts
Match NoMatch Rows in One Rows in Two Full Join Rows All Rows
17 50 50 34 67 84

  

Check out my Jedi SAS Tricks for SAS Users

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

We would need to see the data in these multiple tables in order to provide code. Please provide (a portion of) the data as working SAS data step code, which you can type in yourself, or use these instructions. Make sure the code is working. Do not provide data as screen captures or as file attachments.

--
Paige Miller
SASJedi
SAS Super FREQ

Not really sure what the output table you show is designed to represent, but I can help you get the statistics. Something like this:

 

data one;
   do ID1=1 to 100 by 2;
		output;
	end;
run;
data two;
   do ID2=1 to 100 by 3;
		output;
	end;
run;

proc sql;
title 'Input Table Row Counts';
select 'One' as Table, count(*) as Count from One
  union all
select 'Two',count(*) from Two
;
title 'Table Comparison Counts';
select 
       sum (id1 and id2) as Match
		,sum (sum(missing(id1),missing(id2))) as NoMatch
		,count(ID1) as Rows1 'Rows in One'
		,count(ID2) as Rows2 'Rows in Two'
		,count(*)   as JoinRows 'Full Join Rows'
		,sum(count(ID1),count(ID2)) as Total 'All Rows'
	from one
		full join 
		  two
	on one.id1=two.id2
;
quit;

Produces this result:

Input Table Row Counts

 

Table Count
One 50
Two 34
 
Table Comparison Counts
Match NoMatch Rows in One Rows in Two Full Join Rows All Rows
17 50 50 34 67 84

  

Check out my Jedi SAS Tricks for SAS Users
Tom
Super User Tom
Super User

If that is supposed to a simple cross tab (Think PROC FREQ with two variables) then you are missing on piece of information to complete the table.

 

  YES NO  
YES A B-A B
NO C-A x C-A+x
  C B-A+x  

 

You have given values for A, B and C which will allow you fill in two of the empty cells.  But you can then pick any value for X and generate values for the other three cells.

 

With A=700 and B=800 and C=920 if we set X = 100 then the table will be:

  YES NO  
YES 700 100 800
NO 220 100 320
  920 200  

 

Do you know the overall N?  If so then you do have enough information since C+B-A+x=N  which means that X is N-C-B+A.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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
  • 3 replies
  • 899 views
  • 0 likes
  • 4 in conversation