topic Re: Create a count using conditional statement in SAS Programming

Create a count using conditional statement

CathyVI — Thu, 25 Apr 2024 13:55:40 GMT

Hello Everyone,

I need help trying to find the count for the multi_event column. Here is a sample code and my output. I was able to get the count for event 1-4 only. I also want to count when event is in more than 1 column.

data have;
input event1 event2 event3 event4;
datalines;
. 1 . .
1 1 . .
. . 1 .
1 . . 1
1 . . .
. 1 . .
. . 1 .
. 1 1 1
. . . 1
1 . . .
. . 1 1
;
run;

SAS output

Row 2 and 4 met my criteria for multi_event so they were output in multi_event.

Row 8 and 11 met the criteria but did not output. I want row 8, 11 or any other row with multiple count to be in the multi_event column.

Here is my attempted code (My coding is not classic I will appreciate learning a better way to code it)

data want;
set have;
if event1 ne . and event2=. and event3=. and event4=. then event1only=1;
if event2 ne . and event1=. and event3=. and event4=. then event2only=1;
if event3 ne . and event1=. and event2=. and event4=. then event3only=1;
if event4 ne . and event1=. and event2=. and event3=. then event4only=1;
if event1 ne . and (event2 ne . or  event3 ne . or event4 ne . ) then multi_event=1;
run;

This is my final desired output is to be able to count each column like this with proc tabulate or report.

Event1only	Event2Only	Event3Only	Event4Only	Multi_event
2	2	2	1	3

Thanks

Re: Create a count using conditional statement

FreelanceReinh — Thu, 25 Apr 2024 14:43:06 GMT

Hello @CathyVI,

If the values of event1, event2, etc. are either 1 or missing, you can count the non-missing values with the N function and then use the WHICHN function to populate the "only..." variables. The report is easy with PROC REPORT.

data want(drop=_c);
set have;
array only[4];
_c=n(of event:);
if _c>1 then multi=1;
else if _c=1 then only[whichn(1,of event:)]=1;
run;

proc report data=want;
column only: multi;
run;

Note that variable names only1, only2, etc. with a numeric suffix are more convenient to work with than event1only, etc.: see the array definition and the variable list in the COLUMN statement.

Re: Create a count using conditional statement

ballardw — Thu, 25 Apr 2024 14:50:01 GMT

Can you describe the rule(s) involved? Code that doesn't do what you want without explanation of what is needed makes it hard to suggest changes.

I have to say that without a very clear understanding of what is done in the later analysis that creating a bunch of variables like event1only, event2only, etc. makes me cringe to some extent.

If the only purpose is that "count" (sum might be a better approach)

data example;
   set have;
   array e (*) event1-event4;
   array o (*) eventOnly1 -EventOnly4;
   if sum(of e(*))=1 then  o [ whichn(1,of e(*))] =1;
   else if sum(of e(*))>1 then multi_event=1;
run;

Proc tabulate data=example ;
  var EventOnly: multi_event;
  table  EventOnly: multi_event,
         sum
  ;

run;

Arrays are a one way to group variables for shorthand code. In this case using Sum of the array to tell if only one of the variables has a value of 1, or more, or all missing.

Note the different naming of variables in the Array o definition. If you place a number at the end you can use a list to generate sequentially named variables. Placing a suffix like "only" after that means you have to type them all the time.

The fun bit in the code is the function WHICHN which looks for the first value in a list of others. In this case it looks for a value of 1 in the 4 variables of the array E. The return is the position in the list of that variable. Since we have restricted the use to where only one variable in the list is defined that number can be used as the INDEX for the array o, which is why the whole function call is inside the [ ] brackets. Array can use either () or [] to enclose the index value of the array. I prefer the [ ] as it is easier to see which is the function index boundary when functions are involved.

I used Sum in the tabulate, N would work as well but would require VAR variables not Class because of all the missing values.

Re: Create a count using conditional statement

CathyVI — Thu, 25 Apr 2024 16:59:21 GMT

@FreelanceReinh @ballardw Thank you for explain this. Both codes worked but i have to read carefully to understand your coding.

@FreelanceReinh how can I add "Total" to the output table you created.

Only1	Only2	Only3	Only4	Multi	Total
2	2	2	1	4	11

Re: Create a count using conditional statement

FreelanceReinh — Thu, 25 Apr 2024 17:19:18 GMT

@CathyVI wrote:

@FreelanceReinh how can I add "Total" to the output table you created.

Only1

Only2

Only3

Only4

Multi

Total

2

2

2

1

4

11

I would compute the total in the DATA step and then add the computed variable to the COLUMN statement of the PROC REPORT step:

data want(drop=_c);
set have;
array only[4];
_c=n(of event:);
if _c>1 then multi=1;
else if _c=1 then only[whichn(1,of event:)]=1;
total=sum(of only[*], multi, 0);
run;

proc report data=want;
column only: multi total;
run;

The purpose of the zero in the third argument of the SUM function is to avoid log messages about missing values for (hypothetical) observations with missing values in all event... variables.

Re: Create a count using conditional statement

CathyVI — Thu, 25 Apr 2024 20:15:18 GMT

@FreelanceReinh @ballardw Thank you. I made a 2 silly mistake in my first question. My table is not (.) it is (0). Also, my columns have different names. When I tried to apply your code I did not get the missing value populated.

data have;
input event1 event2 case32 event46;
datalines;
0 1 0 0
1 1 0 0
0 0 1 0
1 0 0 1
1 0 0 0
0 1 0 0
0 0 1 0
0 1 1 1
0 0 0 1
1 0 0 0
0 0 1 1
;
run;

Sorry for the confusion. Please help.

Re: Create a count using conditional statement

ballardw — Thu, 25 Apr 2024 20:22:58 GMT

Looks like the definition of "all missing" would become SUM(of events)=0 .

@CathyVI wrote:

@FreelanceReinh @ballardw Thank you. I made a 2 silly mistake in my first question. My table is not (.) it is (0). Also, my columns have different names. When I tried to apply your code I did not get the missing value populated.
data have;
input event1 event2 case32 event46;
datalines;
0 1 0 0
1 1 0 0
0 0 1 0
1 0 0 1
1 0 0 0
0 1 0 0
0 0 1 0
0 1 1 1
0 0 0 1
1 0 0 0
0 0 1 1
;
run;
Sorry for the confusion. Please help.

Re: Create a count using conditional statement

Reeza — Thu, 25 Apr 2024 20:30:30 GMT

data have;
input event1 event2 case32 event46;
datalines;
0 1 0 0
1 1 0 0
0 0 1 0
1 0 0 1
1 0 0 0
0 1 0 0
0 0 1 0
0 1 1 1
0 0 0 1
1 0 0 0
0 0 1 1
;
run;

data want;
set have;
array _events(*) event: case:;

length event_type $32.;
if sum(of _events(*))>1 then event_type = 'Multi';
else event_type = vname(_events(whichn(1, of _events(*))));

run;

proc tabulate data=want;
class event_type;
table  (event_type all="Total")*N='';
run;

proc freq data=want noprint;
table event_type / out=summary;
run;

proc print data=summary;
run;

Many different ways to get there. 0/1 actually simplify things IMO.

Re: Create a count using conditional statement

ballardw — Thu, 25 Apr 2024 22:00:18 GMT

@ballardw wrote:

Looks like the definition of "all missing" would become SUM(of events)=0 .
@CathyVI wrote:

@FreelanceReinh @ballardw Thank you. I made a 2 silly mistake in my first question. My table is not (.) it is (0). Also, my columns have different names. When I tried to apply your code I did not get the missing value populated.
data have;
input event1 event2 case32 event46;
datalines;
0 1 0 0
1 1 0 0
0 0 1 0
1 0 0 1
1 0 0 0
0 1 0 0
0 0 1 0
0 1 1 1
0 0 0 1
1 0 0 0
0 0 1 1
;
run;
Sorry for the confusion. Please help.

I have to assume "missing" with the above would be all 0 values correct? None are provided to demonstrate.

If that assumption is correct a minor addition to @Reeza's code:

data have;
input event1 event2 case32 event46;
datalines;
0 1 0 0
1 1 0 0
0 0 1 0
1 0 0 1
1 0 0 0
0 1 0 0
0 0 1 0
0 1 1 1
0 0 0 1
1 0 0 0
0 0 1 1
0 0 0 0
;
run;

data want;
set have;
array _events(*) event1 event2 case32 event46;
length event_type $32.;
if sum(of _events(*))>1 then event_type = 'Multi';
else if sum(of _events(*))=0 then event_type = 'Missing';
else event_type = vname(_events(whichn(1, of _events(*))));

run;

proc tabulate data=want;
class event_type;
table  (event_type all="Total")*N='';
run;

Re: Create a count using conditional statement

Patrick — Fri, 26 Apr 2024 02:30:57 GMT

And just for fun here another approach to achieve the same

proc format;
  invalue $case
    '0000' = 0
    '1000' = 1
    '0100' = 2
    '0010' = 3
    '0001' = 4
    other  = 5
    ;
run;

data want(keep= Only1 - Only4 Multi Total);
  set have end=last;

  array src_vars{*} event1 event2 case32 event46;
  array trg_vars{*} Only1 - Only4 Multi (5*0);
  retain trg_vars;

  ind=input(cats(of src_vars[*]),$case.);

  if ind ne 0 then trg_vars[ind]=sum(trg_vars[ind],1);

  if last then 
    do;
      Total=sum(of trg_vars[*]);
      output;
    end;

run;

proc print data=want;
run;

Re: Create a count using conditional statement

FreelanceReinh — Fri, 26 Apr 2024 06:39:01 GMT

@CathyVI wrote:

My table is not (.) it is (0). Also, my columns have different names.

Reeza's definition of variable event_type is a great idea for these new requirements and, of course, using the SUM function instead of the N function.

Re: Create a count using conditional statement

CathyVI — Fri, 26 Apr 2024 16:10:39 GMT

@FreelanceReinh @Patrick @ballardw @Reeza Thank you all. I want to add a final twist to my work. I am sorry if you expected it earlier. I am a step by step learner so I don't want to get confused with too much information at a time. This is my twist.

I want to add "grp" variable to event46. The grp is only available if event46 is available however grp can be available when event46 is present and other event is present e.g., case32 or event2 (this is the multi ).

I would like my outcome to have the event46 outcomes in their respective groups.

data have;
input event1 event2 case32 event46 grp $;
datalines;
0 1 0 0 .
1 1 0 0 .
0 0 1 0 .
1 0 0 1 A
0 0 0 1 A 
1 0 0 0 .
0 0 1 1 C
0 0 0 1 C
0 1 0 0 .
0 0 1 0 .
0 1 1 1 B
0 0 0 1 A 
1 0 0 0 .
0 0 1 1 C
0 0 0 0 .
0 0 0 1 B 
0 0 0 1 B 
;
run;

Note: if case32 and event46 are both present, I still want them in the multi. I think I can do this in pro tabulate by adding grp but did not work

proc tabulate data=want;
class event_type/ order=formatted;
table grp, (event_type all="Total")*N='';
/*table grp*(event_type all="Total")*N='' */
run;

Again, sorry for not addition this details earlier. Thank you so much.

Re: Create a count using conditional statement

ballardw — Fri, 26 Apr 2024 16:23:35 GMT

@CathyVI wrote:

@FreelanceReinh @Patrick @ballardw @Reeza Thank you all. I want to add a final twist to my work. I am sorry if you expected it earlier. I am a step by step learner so I don't want to get confused with too much information at a time. This is my twist.

I want to add "grp" variable to event46. The grp is only available if event46 is available however grp can be available when event46 is present and other event is present e.g., case32 or event2 (this is the multi ).

I would like my outcome to have the event46 outcomes in their respective groups.
data have;
input event1 event2 case32 event46 grp $;
datalines;
0 1 0 0 .
1 1 0 0 .
0 0 1 0 .
1 0 0 1 A
0 0 0 1 A 
1 0 0 0 .
0 0 1 1 C
0 0 0 1 C
0 1 0 0 .
0 0 1 0 .
0 1 1 1 B
0 0 0 1 A 
1 0 0 0 .
0 0 1 1 C
0 0 0 0 .
0 0 0 1 B 
0 0 0 1 B 
;
run;
Note: if case32 and event46 are both present, I still want them in the multi. I think I can do this in pro tabulate by adding grp but did not work
proc tabulate data=want;
class event_type/ order=formatted;
table grp, (event_type all="Total")*N='';
/*table grp*(event_type all="Total")*N='' */
run;
Again, sorry for not addition this details earlier. Thank you so much.

I cannot detect the rule involved with why the first two times you have this the group is A but for the last two lines it is B. You need to provide rules, not examples sometimes.

0 0 0 1

Re: Create a count using conditional statement

CathyVI — Fri, 26 Apr 2024 16:47:34 GMT

@ballardw Thank you. There is no rule involved. The grp variable is part of the original dataset that grouped event46 into A,B,C. event46 is group in the original data, I did not group it but i want my outout into grouping so I see the count of A only, count of B only and count of C only.

Re: Create a count using conditional statement

Tom — Fri, 26 Apr 2024 17:01:20 GMT

Sounds like you want to convert EVENT46 + GRP into some unknown number of new 0/1 variables. Then your new problem is the same as the original problem.

Your example data does not seem to have any ID variable(s) to uniquely identify the observations. So let's add one, let's call it ROW.

data have;
  row+1;
  input event1 event2 case32 event46 grp $;
datalines;
0 1 0 0 .
1 1 0 0 .
0 0 1 0 .
1 0 0 1 A
0 0 0 1 A 
1 0 0 0 .
0 0 1 1 C
0 0 0 1 C
0 1 0 0 .
0 0 1 0 .
0 1 1 1 B
0 0 0 1 A 
1 0 0 0 .
0 0 1 1 C
0 0 0 0 .
0 0 0 1 B 
0 0 0 1 B 
;

Now we can use PROC TRANSPOSE to create EVENT46_A and EVENT46_B etc. Which you can merge back onto your existing data using the key variable(s).


proc transpose data=have prefix=event46_ out=new_vars(drop=_:);
  by row;
  where event46 ;
  id grp;
  var event46;
run;

data new_have;
  merge have new_vars;
  by row;
  drop event46 grp;
run;

Result:

                                            event46_    event46_    event46_
OBS    row    event1    event2    case32        A           C           B

  1      1       0         1         0          .           .           .
  2      2       1         1         0          .           .           .
  3      3       0         0         1          .           .           .
  4      4       1         0         0          1           .           .
  5      5       0         0         0          1           .           .
  6      6       1         0         0          .           .           .
  7      7       0         0         1          .           1           .
  8      8       0         0         0          .           1           .
  9      9       0         1         0          .           .           .
 10     10       0         0         1          .           .           .
 11     11       0         1         1          .           .           1
 12     12       0         0         0          1           .           .
 13     13       1         0         0          .           .           .
 14     14       0         0         1          .           1           .
 15     15       0         0         0          .           .           .
 16     16       0         0         0          .           .           1
 17     17       0         0         0          .           .           1

Re: Create a count using conditional statement

FreelanceReinh — Fri, 26 Apr 2024 17:03:49 GMT

@CathyVI wrote:

I would like my outcome to have the event46 outcomes in their respective groups.

So you want to split the count of "event46" into counts of the subgroups "event46 A", "event46 B", etc.?

A simple solution would be to modify the value of event_type in Reeza's code accordingly, e.g., insert as the last statement in the DATA step creating dataset WANT:

if event_type='event46' then event_type=catx(' ','event46',grp);

However, this might not suffice for a fancier report with, say, headers "A", "B", "C" and a spanning header "event46".

Re: Create a count using conditional statement

CathyVI — Fri, 26 Apr 2024 18:46:55 GMT

@FreelanceReinh @Tom @Patrick Thank you. I am afraid to tag the rest because this is not an efficient way of requesting help. I am solving this task bit by bit. Am very sorry if this is annoying.

data have;
input event1 event2 case32 event46 grp $;
datalines;
0 1 0 0 .
1 2 0 0 .
0 0 1 0 .
1 0 0 1 A
0 0 0 1 A 
1 0 0 0 .
0 0 1 1 C
0 0 0 1 C
0 1 0 0 .
0 0 1 0 .
0 0 2 0 .
0 1 1 1 B
0 0 0 2 A 
2 0 0 0 .
0 0 1 1 C
0 0 0 0 .
0 0 0 1 B 
0 0 0 1 B 

0 2 0 0 .
2 2 0 0 .
0 0 1 0 .
2 0 0 1 A
0 0 0 1 A 
;
run;

The initial data I shared has 0=missing days, 1=any value<=30 days, 2=value between 31-60 days. I mistakenly again forgot the 2. I was only sharing dataset with 0,1. Am sorry.

When I used @Reeza code, the 2 was part of the multi.

data want;
set have;
array _events(*) event1 event2 case32 event46 ;
length event_type $32.;
if sum(of _events(*))>1 then event_type = 'Multiple';
else if sum(of _events(*))=0 then event_type = 'Missing';
else event_type = vname(_events(whichn(1, of _events(*))));
if event_type='event46' then event_type=catx(' ','event46',grp);
run;

This is not what I want. I want 2 to follow the counting process as 1. if you have a single 2 you will be counted differently may if i can name it event1_60, event2_60 or case32_60 and if you have 2 in more than one group you will be multi_60. Again, I know this is inefficient way of requesting help. Am sorry

This is the overall goal am trying to get.

	Event1 only	Event2 only	Case32 only	Event46 A only	Event46 B only	Event46 C Only	Multiple	Total
30 days
31-60 days
Total

Re: Create a count using conditional statement

ballardw — Fri, 26 Apr 2024 21:37:33 GMT

Suggestion: Stop with incremental data descriptions. Give us a full description of all the variables and examples of what you want counted.

There are things that work with missing values that do not work with values of zero.

There are things that work with 1/0 coded values that do not work with 0/1/2/3/4/5 etc.

Almost everything works different with character values than numeric.

So some changes of your values means that logic has to change completely.

Examples: the N function counts non-missing numeric values so a mix of . and 1 means that: N=0 all values missing; N=1 exactly 1 value not missing, N>1 more than one non-missing. Replace the . with 0 and you get 4 (or 5 or 25 if there are 25 variables used).

So another approach. Sum of 1/0 coded numeric variables is the number of 1 values.

Now throw in 2. Neither of the above work. Now each value needs to be tested to "I want 2 to follow the counting process as 1." Are your going to add in 3? then code that tests for 2 won't find it and has to be rewritten again. Is this "count" going to become dependent on more variables that have not been mentioned yet?

Are non-integer values going to be involved with yet another set of rules?

Here's one you aren't likely to be familiar with: what about special missing values? (There are actually 27 different missing values possible in SAS. None would normally be used for summing or such but they can be counted when needed.)

It is one thing to ask different questions about a single description of the data or even conditions in the data. Changing the data content is a completely different story.

Re: Create a count using conditional statement

CathyVI — Fri, 26 Apr 2024 22:12:28 GMT

@ballardw Thanks for the clarity. As I have said this is a mistake-anxiety issue on my part. I am a step-by-step learner but i fail to include all information earlier. lesson learn.

I am not addition another variable or row after this. My data is 0,1,2. Hope you or anyone can help. Thanks again.

Re: Create a count using conditional statement

Patrick — Sat, 27 Apr 2024 00:20:24 GMT

@CathyVI Given that your data is "evolving" I assume you are creating this data yourself as part of some upstream logic. It could be possible that there is a more direct way to get to the report you're after but for us to know you would need to share a representative sample of your "source" data and then describe what information you would like to surface in your report.