Populating count based on missing decode value in result

[smackerz1988]

Hello,

Just looking a way to do this instead of hardcoding a row into a listing. Basically I have this code list.

That will be used for a screen status summary listing.

So there is a value from the codelist (AVAL = 4, AVALC ="Study termination") that is not present in the data that I need to include even if the result will be zero. What is the best way of doing this?. Is it the case of putting the codelist values into an array and using a do loop to pass through AVALC for matching values?. Here is a sample of the code to illustrate the issue and what I was doing prior.

data have;
input SUBJID $ AVAL :8. AVALC :$30.;
infile datalines dlm = '|';
datalines;
1001|1|Did not meet entrance criteria
1002|2|Lost to follow-up
1003|3|Voluntary withdrawal
1004|3|Voluntary withdrawal
1005|5|Adverse event
1006|6|Other
1007|1|Did not meet entrance criteria
run;


proc sql;
   create table have1 as
   select aval as ord2, avalc as col1, count(avalc) as count, 2 as RowOrd
   from have
   group by ord2, col1
   order by ord2, col1;
quit;

[PaigeMiller · Accepted solution]

proc format;
    value avalf 1='Did not meet entrance criteria' 2='Lost to follow-up' 3='Voluntary Withdrawal'
        4='Study Termination' 5="Adverse Event" 6='Other';
run;
data have;
input SUBJID AVAL :8. ;
infile datalines dlm = '|';
format aval avalf.;
datalines;
1001|1
1002|2
1003|3
1004|3
1005|5
1006|6
1007|1
run;


proc report data=have completerows;
    columns aval subjid;
    define aval/group "AVAL" preloadfmt order=internal;
    define subjid/n;
run;

[PaigeMiller]

@smackerz1988 Or even slightly better:

data have;
input SUBJID $ AVAL :8. ;
infile datalines dlm = '|';
format aval avalf.;
datalines;
1001|1
1002|2
1003|3
1004|3
1005|5
1006|6
1007|1
run;
proc report data=have completerows out=a;
    column aval n pctn;
    define aval/group "AVAL" preloadfmt order=internal;
    define n/'Count';
    define pctn/f=percent9.3;
    rbreak after/summarize;
run;

and here the SUBJID can be character or numeric 

[ballardw]

Somewhere you would need to provide the information.

Proc Tabulate and Report have an option PRELOADFMT that will use all of the values of a defined format for a variable in the displayed result. An example with Proc Tabulate:

proc format;
value aval
1='Did not meet entrance criteria'
2='Lost to follow-up'
3='Voluntary withdrawal'
4='Study termination'
5='Adverse event'
6='Other'
;

Proc tabulate data=have;
   class aval /missing preloadfmt;
   format aval aval.;
   tables aval,
          n  colpctn
          /printmiss 
   ;
run;

You could add Misstext='0' after the table option Printmiss to get the N to display a 0.

I use the numeric variable instead of the character as it much easier to maintain the order of appearance in the result table.

[smackerz1988]

Thank you both I completely forgot about the preloadfmt.!