topic Re: Populating count based on missing decode value in result in SAS Programming

Populating count based on missing decode value in result

smackerz1988 — Wed, 19 Oct 2022 14:38:05 GMT

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;

Re: Populating count based on missing decode value in result

PaigeMiller — Wed, 19 Oct 2022 14:51:41 GMT

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;

Re: Populating count based on missing decode value in result

ballardw — Wed, 19 Oct 2022 14:55:16 GMT

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.

Re: Populating count based on missing decode value in result

smackerz1988 — Wed, 19 Oct 2022 15:11:15 GMT

Thank you both I completely forgot about the preloadfmt.!

Re: Populating count based on missing decode value in result

PaigeMiller — Wed, 19 Oct 2022 15:25:33 GMT

@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