I have a two part question.
How would I create the table below using proc report? I can't figure out how to split the cells underneath the targets!
Second question: In my dataset I have one variable 'Target' containing both the target and the strain (Pox S1, Pox S2, etc.) would I still be able to create this table keeping it like that, or should I reshape my data set?
Thanks for the help!
Have a look at the sample code below. The original target variable contents is split into two variables so that we can use the nesting of the across usage.
/*
* create dummy data
*/
data have;
infile cards dlm=",";
input
week : date9.
target : $32.
value : 8.
;
/*
* split target into two variables
*/
target2 = scan(target, 1, " ");
strain = scan(target, 2, " ");
format week date9.;
cards;
27May2024,Pox S1,19
27May2024,Pox S2,25
27May2024,Ebola A,4
27May2024,Ebola B,.
27May2024,Prion B,.
27May2024,Prion H,.
28May2024,Pox S1,10
28May2024,Pox S2,30
28May2024,Ebola A,4
28May2024,Ebola B,1
28May2024,Prion B,2
28May2024,Prion H,.
;
/*
* create a format to print missing values
* as requested
*/
proc format;
value mynumber
. = "not detected"
other = [8.]
;
run;
/*
* proc report code
* nocompletecols only print the actual values
* group usage for week so that multiple rows are reduced to one
* use of " " as label to suppress printing
* target2 and strain used as across, the comma in the column
* statement indicates the nesting
*/
proc report data=have nocompletecols;
column ("week" week) target2, strain, value;
define week / " " group;
define target2 / " " across ;
define strain / " " across;
define value / " " analysis format=mynumber. center;
run;
/*
* alternate way using proc tabulate
*/
proc tabulate data=have format=mynumber.;
class week target2 strain;
var value;
table week = " ", target2 = " " * strain = " " * value = " " * sum = " ";
run;
Have a look at the sample code below. The original target variable contents is split into two variables so that we can use the nesting of the across usage.
/*
* create dummy data
*/
data have;
infile cards dlm=",";
input
week : date9.
target : $32.
value : 8.
;
/*
* split target into two variables
*/
target2 = scan(target, 1, " ");
strain = scan(target, 2, " ");
format week date9.;
cards;
27May2024,Pox S1,19
27May2024,Pox S2,25
27May2024,Ebola A,4
27May2024,Ebola B,.
27May2024,Prion B,.
27May2024,Prion H,.
28May2024,Pox S1,10
28May2024,Pox S2,30
28May2024,Ebola A,4
28May2024,Ebola B,1
28May2024,Prion B,2
28May2024,Prion H,.
;
/*
* create a format to print missing values
* as requested
*/
proc format;
value mynumber
. = "not detected"
other = [8.]
;
run;
/*
* proc report code
* nocompletecols only print the actual values
* group usage for week so that multiple rows are reduced to one
* use of " " as label to suppress printing
* target2 and strain used as across, the comma in the column
* statement indicates the nesting
*/
proc report data=have nocompletecols;
column ("week" week) target2, strain, value;
define week / " " group;
define target2 / " " across ;
define strain / " " across;
define value / " " analysis format=mynumber. center;
run;
/*
* alternate way using proc tabulate
*/
proc tabulate data=have format=mynumber.;
class week target2 strain;
var value;
table week = " ", target2 = " " * strain = " " * value = " " * sum = " ";
run;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: