Creating a shell for a lab table

[Dregerator]

Hi, I'm having a little difficulty creating this shell. 

So lets say i have 36 unique parameters and 10 different visits, and in each of those visits the results of the parameter are classified as low normal high or missing. 

So i'm trying to create a table that is basically a 3 way proc freq of param*visit*result, however, I'm having difficulty setting up the shell. 

I want my table to look like this:

Parameter1                  Total (n=xx) 

   Visit 1

        Low                       n(%)

        Normal                  n(%)

        High                      n(%)

        Missing                 n(%)

   Visit 2

        Low                       n(%)

        Normal                  n(%)

        High                      n(%)

        Missing                 n(%)

....

   Visit 10

        Low                       n(%)

        Normal                  n(%)

        High                      n(%)

        Missing                 n(%)

Parameter 2

   Visit 1

        Low                       n(%)

        Normal                  n(%)

        High                      n(%)

        Missing                 n(%)

   Visit 2

        Low                       n(%)

        Normal                  n(%)

        High                      n(%)

        Missing                 n(%)

Could anyone help me out as to how to create this shell?

Thank you in advance

[andreas_lds]

It should be possible to create such layout with proc report or a data-null-step using rwi-comands.I would start with proc report - most likely less code to write. Can you post data in usable form, so that we see what you actually have?

[Dregerator]

Hi, I've attached a small subset of the data but that is essentially what i'm working with. the results in my table need to counts of how many total results there were per visit and then how many are in low(% of total), how many are normal(% of total), how many are in high (% of total), how many are in missing (% of total). 

[Kurt_Bremser]

"Usable data" means something like this:

data have;
input avisitn param &$50. result $;
datalines;
65  Activated Partial Thromboplastin Time()  High
65  Activated Partial Thromboplastin Time()  High
65  Activated Partial Thromboplastin Time()  High
;

This allows us to quickly recreate your data, without leaving any doubts about variable types and other attributes. Adapt the input statement as needed so the data step reflects your actual variable attributes (run a proc contents on your dataset to determine this)

[ed_sas_member · Accepted solution]

Hi @Dregerator 

I am thinking about something like this.

Please test it and let me know if that does meet you need.

data have;
	infile datalines dlm=",";
input avisitn param:$50. result $;
datalines;
65,Activated Partial Thromboplastin Time(),High
65,Activated Partial Thromboplastin Time(),High
...
;
run;

/*** Compute formats ************/

proc format;
	value avisitn 65 = "Visit1" 78 = "Visit2" 91 = "Visit3"; /* <--- adapt the codelist */
run;

/*** Compute &total */

%let total= <Your value>; /* <--- compute the total according to your specifications througth a proc sql*/

/*** Compute frequencies ********/

proc freq data=have noprint;
	tables param*avisitn*result / outpct sparse out=lb_freq (keep= param avisitn result count pct_row);
run;

/*** Custom tables **************/

* Frequencies;
data lb_freq2;
	
	length key text $ 200.;
	set lb_freq;
	
	count_pct = cat(count," (",strip(put(pct_row/100,percent8.1)),")"); /* <--- Check if the percents are well calculated*/
	
	if result = "Low" then resultn = 1;
	else if result = "Normal" then resultn = 2;
	else if result = "High" then resultn = 3;
	
	key = catx("_",param,avisitn,resultn);
	text = cats("#{nbspace 10}",result);
	
	keep key text count_pct param;
run;

proc sort data=lb_freq2;
	by key;
run;

* Hierarchies: parameter level;
proc sort data=have out=list_parameters (keep=param) nodupkey;
	by param;
run;

data list_parameters2;
	
	length key text $ 200.;
	set list_parameters;
	
	key = param;
	text= param;
	
	keep key text param;
run;

proc sort data=list_parameters2;
	by key;
run;

* Hierarchies: visit level;
proc sort data=have out=list_parameters_visits (keep=param avisitn) nodupkey;
	by param avisitn;
run;

data list_parameters_visits2;
	
	length key text $ 200.;
	set list_parameters_visits;
	
	key = catx("_",param,avisitn);
	text= cats("#{nbspace 5}",put(avisitn, avisitn.));
	
	keep key text param;
run;

proc sort data=list_parameters_visits2;
	by key;
run;

/*** Merge all tables ***********/

data forreport;
	merge list_parameters2 list_parameters_visits2 lb_freq2;
	by key text;
run;	

/* Produce report ***************/

ods escapechar='#';

proc report data=forreport nowd split="|";

	column param key text count_pct;

	define param / group noprint;
	define key / order noprint;
	define text / display "Parameter";
	define count_pct / display "Total|(n=&total.)";

	compute before param;
		line "";
	endcomp;

run;