Summarizing Data

[Ranjeeta]

data All_DAD_SU_DEN;
set All_DAD;
where OSN_Cohort__DAD_Only_ in ('1');
if DAD_Has_Project_340_Information = 1 ;
run;
/*12,585*/


/**NUME**/

data All_DAD_SU_NUME;
set All_DAD_SU_DEN;
where OSN_Cohort__DAD_Only_ in ('1');
if DAD_Has_Project_340_Information = 1 ;
where DAD_Admission_to_a_Stroke_Unit in ('Y');
run;/*7,141*/

I would appreciate some advice on how to proceeed further:

I want to summarize the information from the 2 datasets such that by facility I count the no of records from the two datasets 

and divide the nume/denominatior i.e. from these 2 datasets:  All_DAD_SU_NUME/All_DAD_SU_DEN

[Kurt_Bremser]

Please supply example data in usable form (data step with datalines), and the expected outcome for this.

[ballardw]

First thing is you may want to check your set All_DAD_SU_NUME for the values of OSN_Cohort__DAD_Only_. Two Where statements in a data step may not do what you expect.

And why the mix of Where and IF?

data All_DAD_SU_NUME;
set All_DAD_SU_DEN;
where OSN_Cohort__DAD_Only_ in ('1') And
  DAD_Has_Project_340_Information = 1 And
  DAD_Admission_to_a_Stroke_Unit in ('Y');
run;

Might be more appropriate.

If this were my data instead of "counting" from two different data sets I would add a variable that indicates whether a particular record is of interest, usually with a flag variable that has values of 1 (for of interest) and 0 for not. Then a Sum of the 1's is the number of records of interest, N is the total number of records and a mean would be the percent of 1's.

Something like (obviously untested code because no data provided)

data All_DAD_SU_NUME;
   set All_DAD_SU_DEN;
   Su_nume =( OSN_Cohort__DAD_Only_ in ('1') And
     DAD_Has_Project_340_Information = 1 And
     DAD_Admission_to_a_Stroke_Unit in ('Y') )
  ;
run;

proc tabulate data= All_DAD_SU_NUME;
   class facility;
   var Su_nume;
   table facility,
         su_nume=' '*(n='Facility Records' sum='Su_nume records'*f=best8.
                  mean='Percent Su_nume'*f=percent8.1)
   ;
run;

Part of the reason this works is that SAS will assign a numeric 1 for "true" values of a logical comparison and 0 for false.

[Ranjeeta]

I have updloaded sample data with the result desired inanother tab 

Thankyou for the advice How would i modify the proc tab to acheive the same?

Regards

[Tom]

Your XLSX sheet is probably too big for this forum.  When I tried to look at it.

Document preview of Copy of Test_ProcTab (002).xlsx

Error displaying attachment content

Most people do not want to download foreign files from the web.  Much better to just paste the values as plain text into your post using the Insert Code button. Only supply the minimum number of variables and observations needed to describe the issue.

[Ranjeeta]

Data and Result Desired:

OSN Cohort (DAD Only)	OSN Cohort (DAD & NAC)	DAD Discharge Date	DAD Discharge Fiscal Year	DAD Discharge Fiscal Quarter	DAD Institution Name	DAD Facility LHIN Name	DAD Has Project 340 Information	DAD Administration of Acute Thrombolysis	DAD Admission to a Stroke Unit
1	1	May 2020	FY 2020/21	1	ABC	TC	1	Yes	Y
1	1	May 2020	FY 2020/21	1		SE	1	No	Y
1	1	May 2020	FY 2020/21	1		TC	1	No	N
1	1	May 2020	FY 2020/21	1		TC	1	Yes	Y
1	1	Apr 2020	FY 2020/21	1		SE	1	No	Y
1	1	May 2020	FY 2020/21	1		SE	1	No	Y
1	1	May 2020	FY 2020/21	1		SE	1	Yes	Y
1	1	May 2020	FY 2020/21	1		SE	1	No	Y
1	1	Apr 2020	FY 2020/21	1		SE	1	No	Y
1	1	May 2020	FY 2020/21	1		SE	1	No	Y
1	1	Apr 2020	FY 2020/21	1		SE	1	No	Y
0	0	May 2020	FY 2020/21	1		TC	1		N
1	1	Apr 2020	FY 2020/21	1		TC	1	No	N
1	1	Apr 2020	FY 2020/21	1		TC	1	Yes	Y
1	1	May 2020	FY 2020/21	1		TC	1		Y

	FY 2021	Quarter
LHIN	Apr 2020	May 2020	1
TC	2 (50%)	4 (75%)	6 (66%)
SE		5(100%)	5(100%)