IF THEN/ELSE help

[chandler]

action status reports
Code_1	Code_2	A1_cnt	A2_cnt	APPLICATION_ID	ACTIVITY_DATETIME	datetime_A1	datetime_a2
A1		1	0	12345678912345	24SEP2012:16:13:05	24SEP2012:16:13:05	.
A1		3	0	12345678912346	25OCT2012:08:44:55	25OCT2012:08:44:55	.
A1		3	0	12345678912347	11SEP2012:12:05:07	11SEP2012:12:05:07	.
A1		1	0	12345678912348	27SEP2012:10:36:30	27SEP2012:10:36:30	.
A1		1	0	12345678912349	06SEP2012:11:17:53	06SEP2012:11:17:53	.
A1		2	0	12345678912350	10SEP2012:16:07:53	10SEP2012:16:07:53	.
A1		2	0	12345678912351	11SEP2012:12:32:37	11SEP2012:12:32:37	.
A1		1	0	12345678912352	26SEP2012:14:48:19	26SEP2012:14:48:19	.
A1		1	0	12345678912353	12SEP2012:17:28:08	12SEP2012:17:28:08	.
A1		1	0	12345678912354	12SEP2012:17:29:05	12SEP2012:17:29:05	.
A1		2	0	12345678912355	26OCT2012:11:00:16	26OCT2012:11:00:16	.
A1		1	0	12345678912356	06SEP2012:11:21:31	06SEP2012:11:21:31	.
A1		2	0	12345678912357	18SEP2012:10:11:19	18SEP2012:10:11:19	.
A1		1	0	12345678912358	07SEP2012:08:59:46	07SEP2012:08:59:46	.
A1		2	0	12345678912359	20SEP2012:12:18:50	20SEP2012:12:18:50	.
A1		1	0	12345678912360	25SEP2012:08:33:52	25SEP2012:08:33:52	.
A1		1	0	12345678912361	19SEP2012:11:37:19	19SEP2012:11:37:19	.
A1		1	0	12345678912362	25SEP2012:10:29:05	25SEP2012:10:29:05	.
A1		1	0	12345678912363	06SEP2012:10:52:45	06SEP2012:10:52:45	.
A1		1	0	12345678912364	06SEP2012:16:03:29	06SEP2012:16:03:29	.
A1		1	0	12345678912365	05SEP2012:20:09:11	05SEP2012:20:09:11	.
A1		3	0	12345678912366	07SEP2012:10:45:59	07SEP2012:10:45:59	.
A1		1	0	12345678912367	07SEP2012:10:46:23	07SEP2012:10:46:23	.
A1		1	0	12345678912368	06SEP2012:07:45:56	06SEP2012:07:45:56	.
A1		1	0	12345678912369	11OCT2012:18:59:40	11OCT2012:18:59:40	.
A1		1	0	12345678912370	14SEP2012:11:51:13	14SEP2012:11:51:13	.
A1		1	0	12345678912371	11SEP2012:08:36:52	11SEP2012:08:36:52	.
A1		2	0	12345678912372	14SEP2012:15:56:51	14SEP2012:15:56:51	.
A1		2	0	12345678912373	14SEP2012:15:57:44	14SEP2012:15:57:44	.
A1		1	0	12345678912374	14SEP2012:15:57:54	14SEP2012:15:57:54	.
A1		1	0	12345678912375	14SEP2012:15:58:03	14SEP2012:15:58:03	.
A1		1	0	12345678912376	06SEP2012:08:14:03	06SEP2012:08:14:03	.
A1		1	0	12345678912377	06SEP2012:08:12:52	06SEP2012:08:12:52	.
A1		1	0	12345678912378	12SEP2012:16:01:29	12SEP2012:16:01:29	.
A1		1	0	12345678912379	02OCT2012:09:41:05	02OCT2012:09:41:05	.
A1		2	0	12345678912380	21SEP2012:13:14:26	21SEP2012:13:14:26	.
A1		2	0	12345678912381	12SEP2012:17:08:18	12SEP2012:17:08:18	.
A1		2	0	12345678912382	17OCT2012:16:58:54	17OCT2012:16:58:54	.
A1		1	0	12345678912383	17SEP2012:18:42:49	17SEP2012:18:42:49	.
A1		1	0	12345678912384	17SEP2012:09:24:42	17SEP2012:09:24:42	.
A1		1	0	12345678912385	27SEP2012:11:32:58	27SEP2012:11:32:58	.
A1		2	0	12345678912386	05OCT2012:09:36:00	05OCT2012:09:36:00	.
A1		1	0	12345678912387	05OCT2012:11:37:05	05OCT2012:11:37:05	.
A1		1	0	12345678912388	27SEP2012:09:02:01	27SEP2012:09:02:01	.
A1		2	0	12345678912389	20SEP2012:07:19:42	20SEP2012:07:19:42	.
A1		1	0	12345678912390	20SEP2012:07:21:03	20SEP2012:07:21:03	.
A1		1	0	12345678912391	20SEP2012:07:21:14	20SEP2012:07:21:14	.
A1		1	0	12345678912392	20SEP2012:07:21:24	20SEP2012:07:21:24	.
A1		2	0	12345678912393	26SEP2012:08:46:07	26SEP2012:08:46:07	.
A1		2	0	12345678912394	26SEP2012:08:43:30	26SEP2012:08:43:30	.
A1		1	0	12345678912395	21SEP2012:13:12:38	21SEP2012:13:12:38	.
A1		1	0	12345678912396	21SEP2012:08:30:01	21SEP2012:08:30:01	.
A1		2	0	12345678912397	27SEP2012:14:33:16	27SEP2012:14:33:16	.
A1		2	0	12345678912398	25SEP2012:12:03:24	25SEP2012:12:03:24	.
A1		1	0	12345678912399	12OCT2012:13:19:23	12OCT2012:13:19:23	.
A1		1	0	12345678912400	20SEP2012:07:21:38	20SEP2012:07:21:38	.
A1		1	0	12345678912401	24OCT2012:09:34:29	24OCT2012:09:34:29	.
A1		1	0	12345678912402	24OCT2012:09:33:48	24OCT2012:09:33:48	.
A1		1	0	12345678912403	24OCT2012:09:34:08	24OCT2012:09:34:08	.
A1		1	0	12345678912404	04OCT2012:14:03:02	04OCT2012:14:03:02	.
A1		1	0	12345678912405	01OCT2012:08:26:13	01OCT2012:08:26:13	.
A1		2	0	12345678912406	31OCT2012:14:35:48	31OCT2012:14:35:48	.
A1		1	0	12345678912407	24SEP2012:16:11:11	24SEP2012:16:11:11	.
A1		2	0	12345678912408	12OCT2012:16:51:24	12OCT2012:16:51:24	.
A1		1	0	12345678912409	25OCT2012:16:29:47	25OCT2012:16:29:47	.
A1		2	0	12345678912410	27SEP2012:09:29:18	27SEP2012:09:29:18	.
A1		1	0	12345678912411	23OCT2012:16:18:40	23OCT2012:16:18:40	.
A1		3	0	12345678912412	11OCT2012:18:15:04	11OCT2012:18:15:04	.
A1		2	0	12345678912413	18OCT2012:18:01:02	18OCT2012:18:01:02	.
A1		1	0	12345678912414	02OCT2012:10:34:01	02OCT2012:10:34:01	.
A1		2	0	12345678912415	27SEP2012:11:59:38	27SEP2012:11:59:38	.
A1		1	0	12345678912416	28SEP2012:16:15:26	28SEP2012:16:15:26	.
A1		1	0	12345678912417	26OCT2012:08:51:12	26OCT2012:08:51:12	.
A1		1	0	12345678912418	21SEP2012:18:48:28	21SEP2012:18:48:28	.
A1		2	0	12345678912419	01OCT2012:09:34:58	01OCT2012:09:34:58	.
A1		1	0	12345678912420	24SEP2012:16:07:54	24SEP2012:16:07:54	.
A1		1	0	12345678912421	31OCT2012:13:41:25	31OCT2012:13:41:25	.
A1		1	0	12345678912422	08NOV2012:15:35:03	08NOV2012:15:35:03	.
A1		3	0	12345678912423	12OCT2012:13:42:22	12OCT2012:13:42:22	.
A1		2	0	12345678912424	11OCT2012:16:28:02	11OCT2012:16:28:02	.
A1		2	0	12345678912425	02NOV2012:10:19:41	02NOV2012:10:19:41	.
A1		2	0	12345678912426	25OCT2012:10:06:59	25OCT2012:10:06:59	.
A1		1	0	12345678912427	25OCT2012:10:44:42	25OCT2012:10:44:42	.
A1		1	0	12345678912428	08NOV2012:10:26:03	08NOV2012:10:26:03	.
A1		1	0	12345678912429	05OCT2012:10:33:34	05OCT2012:10:33:34	.
A1		1	0	12345678912430	11OCT2012:17:41:52	11OCT2012:17:41:52	.
A1		1	0	12345678912431	28SEP2012:15:25:45	28SEP2012:15:25:45	.
A1		2	0	12345678912432	18OCT2012:11:59:41	18OCT2012:11:59:41	.
A1		1	0	12345678912433	24OCT2012:09:37:56	24OCT2012:09:37:56	.
A1		1	0	12345678912434	02OCT2012:15:34:52	02OCT2012:15:34:52	.
A1		2	0	12345678912435	18OCT2012:15:46:06	18OCT2012:15:46:06	.
A1		1	0	12345678912436	25OCT2012:10:02:53	25OCT2012:10:02:53	.
A1		1	0	12345678912437	16OCT2012:13:23:21	16OCT2012:13:23:21	.
A1		1	0	12345678912438	31OCT2012:13:50:22	31OCT2012:13:50:22	.
A1		1	0	12345678912439	12OCT2012:14:30:14	12OCT2012:14:30:14	.
A1		1	0	12345678912440	08NOV2012:10:26:08	08NOV2012:10:26:08	.
A1		1	0	12345678912441	30OCT2012:17:41:10	30OCT2012:17:41:10	.
A1		2	0	12345678912442	05OCT2012:11:01:22	05OCT2012:11:01:22	.
A1		2	0	12345678912443	30OCT2012:16:45:22	30OCT2012:16:45:22	.
A1		1	0	12345678912444	08NOV2012:10:24:10	08NOV2012:10:24:10	.
A1		1	0	12345678912445	08NOV2012:10:24:20	08NOV2012:10:24:20	.
A1		1	0	12345678912446	24OCT2012:09:35:07	24OCT2012:09:35:07	.
A1		2	0	12345678912447	22OCT2012:11:18:07	22OCT2012:11:18:07	.
A1		3	0	12345678912448	23OCT2012:13:55:41	23OCT2012:13:55:41	.
A1		1	0	12345678912449	02NOV2012:11:32:11	02NOV2012:11:32:11	.
A1		1	0	12345678912450	02NOV2012:11:32:19	02NOV2012:11:32:19	.
A1		2	0	12345678912451	25OCT2012:15:39:09	25OCT2012:15:39:09	.
A1		2	0	12345678912452	05NOV2012:11:37:29	05NOV2012:11:37:29	.
A1		1	0	12345678912453	26OCT2012:16:02:57	26OCT2012:16:02:57	.
A1		2	0	12345678912454	22OCT2012:12:06:47	22OCT2012:12:06:47	.
A1		1	0	12345678912455	25OCT2012:16:38:58	25OCT2012:16:38:58	.
A1		1	0	12345678912456	08NOV2012:11:02:29	08NOV2012:11:02:29	.
A1		2	0	12345678912457	25OCT2012:12:31:49	25OCT2012:12:31:49	.
A1		2	0	12345678912458	25OCT2012:09:43:57	25OCT2012:09:43:57	.
A1		2	0	12345678912459	25OCT2012:09:45:00	25OCT2012:09:45:00	.
A1		1	0	12345678912460	18OCT2012:11:50:44	18OCT2012:11:50:44	.
A1		1	0	12345678912461	24OCT2012:09:38:52	24OCT2012:09:38:52	.
A1		2	0	12345678912462	24OCT2012:10:54:48	24OCT2012:10:54:48	.
A1		1	0	12345678912463	02NOV2012:11:32:29	02NOV2012:11:32:29	.
A1		2	0	12345678912464	07NOV2012:12:35:50	07NOV2012:12:35:50	.
A1		1	0	12345678912465	01NOV2012:12:34:55	01NOV2012:12:34:55	.
A1		2	0	12345678912466	24OCT2012:12:03:07	24OCT2012:12:03:07	.
A1		1	0	12345678912467	07NOV2012:16:50:54	07NOV2012:16:50:54	.
A1		1	0	12345678912468	25OCT2012:10:05:23	25OCT2012:10:05:23	.
A1		1	0	12345678912469	25OCT2012:17:28:13	25OCT2012:17:28:13	.
A1		2	0	12345678912470	19OCT2012:09:30:50	19OCT2012:09:30:50	.
A1		1	0	12345678912471	24OCT2012:15:55:21	24OCT2012:15:55:21	.
A1		1	0	12345678912472	30OCT2012:17:15:31	30OCT2012:17:15:31	.
A1		3	0	12345678912473	30OCT2012:16:59:47	30OCT2012:16:59:47	.
A1		2	0	12345678912474	25OCT2012:08:55:35	25OCT2012:08:55:35	.
A1		1	0	12345678912475	02NOV2012:15:59:27	02NOV2012:15:59:27	.
A1		1	0	12345678912476	02NOV2012:15:59:44	02NOV2012:15:59:44	.
A1		1	0	12345678912477	02NOV2012:15:59:51	02NOV2012:15:59:51	.
A1		1	0	12345678912478	31OCT2012:10:10:26	31OCT2012:10:10:26	.
A1		1	0	12345678912479	31OCT2012:14:50:23	31OCT2012:14:50:23	.
A1		2	0	12345678912480	01NOV2012:15:16:34	01NOV2012:15:16:34	.
A1		1	0	12345678912481	09NOV2012:17:59:47	09NOV2012:17:59:47	.
A1		2	0	12345678912482	08NOV2012:10:24:30	08NOV2012:10:24:30	.

This is my code, see attachment.   The output above is partially correct.  I need the Code_2, A2_cnt, and datetime_a2 columns to populate.  What am I doing wrong?

[art297]

You have nested two conditions that can't be met at the same time.  Rather than:

  if cch_activity_code = 'A1' then do;

    A1_cnt+1 ;

    Code_1 = 'A1';

    format Code_1 $2. ;

    datetime_A1 = activity_datetime ;

    format datetime_A1 datetime20. ;

    if cch_activity_code = 'A2' then do;

      A2_cnt+1 ;

      Code_2 = 'A2' ;

      format Code_2 $2. ;

      datetime_a2 = activity_datetime ;

      format datetime_A2 datetime20. ;

    end;

  end;

move the last end to the end of the first condition.  i.e.:

  if cch_activity_code = 'A1' then do;

    A1_cnt+1 ;

    Code_1 = 'A1';

    format Code_1 $2. ;

    datetime_A1 = activity_datetime ;

    format datetime_A1 datetime20. ;

  end;

  if cch_activity_code = 'A2' then do;

    A2_cnt+1 ;

    Code_2 = 'A2' ;

    format Code_2 $2. ;

    datetime_a2 = activity_datetime ;

    format datetime_A2 datetime20. ;

end;

Plus, why do you have the format statements throughout the program.  The code would be easier to read, and possibly improve performance, if they were all stated at the start of the program.

[ballardw]

See note in bold below.

data Appeal_totals (keep=application_id A1_cnt A2_cnt Code_1 Code_2 datetime_A1 datetime_A2 activity_datetime) ;

length Code_1 Code_2 $ 2 ;

retain A1_cnt A2_cnt 0 ;

set example ;

by application_id ;

if cch_activity_code = 'A1' then

do;

A1_cnt+1 ;

Code_1 = 'A1'; format Code_1 $2. ;

datetime_A1 = activity_datetime ;

format datetime_A1 datetime20. ;

/* the following piece never executes as it is within the code='A1' section of code you probably need an

end statement before here. Also format assignments aren't conditional so it would make the code a bit easier to read if you move the format statements out of the IF/THEN code.  You may also want

ELSE IF cch_activity_code = 'A2' */

if cch_activity_code = 'A2' then

do;

A2_cnt+1 ;

Code_2 = 'A2' ; format Code_2 $2. ;

datetime_a2 = activity_datetime ;

format datetime_A2 datetime20. ;

end;

end;

if last.application_id then

do;

output ;

A1_cnt = 0 ;

A2_cnt = 0 ;

end;

run;

[Reeza]

You've had your mistake pointed out.

Heres an alternative code that uses arrays and is slightly more compact. I didn't test it unfortunately...

data Appeal_totals (keep=application_id A1_cnt A2_cnt Code_1 Code_2 datetime_A1 datetime_A2 activity_datetime) ;
length Code_1 Code_2 $ 2 ;
  retain A1_cnt A2_cnt 0 ;
  format a1_count a2_count 8.;
  format code_1 code_2 $2.;
  format datetime_A1 datetime_a2 datetime20.;


  set example ;
  by application_id ;

array cnts(*) a1_count a2_count;
array dtime(*) datetime_a1 datetime_a2;
array cde(*) $ code_1 code_2;

code=substr(cch_activity_code, 2, 1);

cnts(code)+1;
dtime(code)=activity_datetime;
cde(code)=cch_activity_code;

  if last.application_id then do;
       output ;    
    A1_cnt = 0 ;
    A2_cnt = 0 ;
end;     
run;

[chandler]

Thanks, Reeza. 

I tried your suggested code, above, and got this error.  Can you help me fix this?  I don't know enough about arrays at this point.

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column).

11155:7 11156:5

ERROR: Array subscript out of range at line 11155 column 1.

NOTE: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 2 observations read from the data set WORK.EXAMPLE.

WARNING: The data set WORK.APPEAL_TOTALS may be incomplete. When this step was stopped there

were 0 observations and 8

variables.

WARNING: Data set WORK.APPEAL_TOTALS was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

　

NOTE: Remote submit to REMHOST complete.

MLOGIC(RSUBMIT): Ending execution.

[chandler]

Sorry, for the incomplete message.

Line 11155 column 1 is this line in your code.

cnts(code)+1;

[Reeza]

What's code (the variable) at that line?

Is it 1 or 2? Those are the only possible variables you've identified, if its something else ie 3 or missing that would be an issue that you haven't mentioned or is being dealt with, in my code or your initial code.

[art297]

You might be able to resolve the problem if you add one bit of logic from your original code:

data Appeal_totals (keep=application_id A1_cnt A2_cnt Code_1 Code_2 datetime_A1 datetime_A2 activity_datetime) ;
length Code_1 Code_2 $ 2 ;
  retain A1_cnt A2_cnt 0 ;
  format a1_count a2_count 8.;
  format code_1 code_2 $2.;
  format datetime_A1 datetime_a2 datetime20.;

  set example ;
  by application_id ;

  array cnts(*) a1_count a2_count;
  array dtime(*) datetime_a1 datetime_a2;
  array cde(*) $ code_1 code_2;

  code=substr(cch_activity_code, 2, 1);

  /* added ->*/ if code in ('A1','A2) then do;

      cnts(code)+1;
      dtime(code)=activity_datetime;
      cde(code)=cch_activity_code;

/*added ->*/   end;

  if last.application_id then do;
       output ;    
    A1_cnt = 0 ;
    A2_cnt = 0 ;
  end;     
run;

Re: IF THEN/ELSE help