Hello All,
I may have to resort to proc report but if I can help it I would like to this in Proc Tabulate if I could.
Below is an example of the table I want and get with proc report. What makes this table difficult in proc tabulate is the three different denominators used to create this table.
All Randomized, Subjects with at least 1 AE and then the breakdown of the AE's them selves. I am able to create this table in proc tabulate but I have to include the missing option which provided rows with a missing (.) value that would rather not have. I want to Suppress any row that Proc Tabulate produces that is not seen below.
I have attached a sample dataset and a piece of code. The first proc tabulate procedure is the only one that is in question here. The other two will be built based on if I can solve the first.
| Treatment Group |
| |||
Selected or Anticipated Adverse Events and Safety Outcomes | Expected Event Rate | A | B | C | Total1 |
All Randomized as of 22Mar2021 |
| 44.0 | 62.0 | 61.0 | 167 (100.0) |
# of subjects with at least 1 event |
| 25 (63.0 | 35 (64.0 | 37 (71.0 | 97 (58.1) |
Pneumonia | 40-60% | 3 (8.0) | 6 (11.0) | 7 (13.0) | 16 (9.6) |
Other infections (including UTI and bacteremia) | 17-40% |
| 2 (4.0) | 5 (10.0) | 7 (4.2) |
Malignant cardiac arrhythmia | 46-65% | 2 (5.0) | 5 (9.0) | 5 (10.0) | 12 (7.2) |
Seizures | 20-40% | 3 (8.0) | 1 (2.0) |
| 4 (2.4) |
Neurological worsening | 30-50% | 18 (45.0 | 24 (44.0 | 19 (37.0 | 61 (36.5) |
Electrolyte abnormalities | 70-80% | 2 (5.0) | 4 (7.0) | 7 (13.0) | 13 (7.8) |
Venous thrombotic disease | 10-20% |
| 1 (2.0) | 1 (2.0) | 2 (1.2) |
Coagulopathies | 10-20% | 1 (3.0) |
| 3 (6.0) | 4 (2.4) |
Does it have to be PROC TABULATE? I've solved a similar requirement using PROC PRINT where I placed numbers, parentheses and carriage returns into strings (all output destination dependent) printing it to my ODS destination.
Does it have to be PROC TABULATE? I've solved a similar requirement using PROC PRINT where I placed numbers, parentheses and carriage returns into strings (all output destination dependent) printing it to my ODS destination.
@GreyJoy wrote:
Hello All,
I may have to resort to proc report but if I can help it I would like to this in Proc Tabulate if I could.
Below is an example of the table I want and get with proc report. What makes this table difficult in proc tabulate is the three different denominators used to create this table.
All Randomized, Subjects with at least 1 AE and then the breakdown of the AE's them selves. I am able to create this table in proc tabulate but I have to include the missing option which provided rows with a missing (.) value that would rather not have. I want to Suppress any row that Proc Tabulate produces that is not seen below.
I have attached a sample dataset and a piece of code. The first proc tabulate procedure is the only one that is in question here. The other two will be built based on if I can solve the first.
You do not describe what any of the "denominators" might be, or for which varible.
The tabulate code in your attachment does not show any place you are attempting percentages only sums.
libname sas xlsx "INSERT _ PATH\suppress rows in tabulate date.xlsx"; proc sort data=sas.sheet1 out=ae_dataset_sorted1 nodupkey; by zSubjectID f104q12; run; proc format; value names 1="Pneumonia" 2="Other infections (including UTI and bacteremia)" 3="Malignant cardiac arrhythmia" 4="Seizures" 5="Neurological worsening" 6="Electrolyte abnormalities" 7="Venous thrombotic disease" 8="Coagulopathies" 98="NA" .1="All Randomized as of &reportdt2" .5="# of Subjects with at least 1 Events" ; run; /* Over view of Observed vs Expected Coutcomes */ proc tabulate data=work.ae_dataset_sorted1 missing out=table; /* where f104q12 in(1:8);*/ class f104q12 F102zTreatmentID rando a_ae ; var count ; table rando a_ae f104q12,F102zTreatmentID*count all="total" / ; format f104q12 names.; run; /**************************************************************************************/ /********* Attempt at bypassing the problem. This is Last Resort **************/ /**************************************************************************************/ /* data table_2;*/ /* set work.table;*/ /* if rando =1 then f104q12=.1;*/ /* if a_ae=1 then f104q12=.5;*/ /* if _type_ in(0110) and rando ne . then output;*/ /* if _type_ in(0101) and a_ae ne . then output;*/ /* if _type_ in(1100) and f104q12 not in(. 98) then output;*/ /* run;*/ /**/ /* proc tabulate data=work.table_2;*/ /* class f104q12 F102zTreatmentID;*/ /* var count_sum;*/ /* table f104q12="",F102zTreatmentID*count_sum sum="total";*/ /* run;*/ /**************************************************************************************/ proc sort data=work.AE_dataset out=ae_dataset_sorted2 nodupkey; by zSubjectID SOCname PTname; run; /* ALL ADVERSE EVENTS */ proc tabulate data=work.ae_dataset_sorted2 ; class SOCname PTname F102zTreatmentID; var count; table SOCname*PTname,F102zTreatmentID*count all="total" / ; run; /* ALL SERIOUS ADVERSE EVENTS */ proc tabulate data=work.ae_dataset_sorted2; where serious_sae=1; class SOCname PTname F102zTreatmentID; var count; table SOCname*PTname,F102zTreatmentID*count all="total"; run;
Without an input data set hard to tell.
I do multiple things by making the data work for me an a few simple bits. One thing is if you have a variable that is coded 1/0 (numeric) for yes/no then an N statistic tells you how many records have the value set, Sum tells you how many records have the 1 value (yes) set, and the mean will give you a decimal percent.
So in a data set you can create a number variables and use those. Example you should be able to run as the SASHELP.Class data set should be included in your install.
data example; set sashelp.class; overweight = (weight > 100); reallyoverweight = (weight>120); run; proc tabulate data=example; class sex age; var overweight reallyoverweight; tables all='All children' sex age sex*age, (overweight reallyoverweight)*(n='# measurements' sum='Number'*f=f5. mean='%'*f=percent8.1) ; label overweight='Weight > 100' reallyoverweight='Weight > 120' ; run;
IF you had shown an attempt to calculate percentages we might be able to help a bit more. Tabulate for the PCTN or PCTSUM statistic will allow you to provide another variable to use as denominator.
Not great but demonstrates a syntax example.
proc tabulate data=example ; class sex age; table sex, age*( n pctn<sex>); run;
I do have an input dataset. I provided it right next to the code.
@GreyJoy wrote:
I do have an input dataset. I provided it right next to the code.
Many users here don't want to download Excel files because of virus potential, organization policy may prohibit downloading such and others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.