The SAS Output Delivery System and reporting techniques

IF THEN/ELSE help

Reply
Contributor
Posts: 61

IF THEN/ELSE help

    action status reports

Code_1Code_2A1_cntA2_cntAPPLICATION_IDACTIVITY_DATETIMEdatetime_A1datetime_a2
A1 101234567891234524SEP2012:16:13:0524SEP2012:16:13:05.
A1 301234567891234625OCT2012:08:44:5525OCT2012:08:44:55.
A1 301234567891234711SEP2012:12:05:0711SEP2012:12:05:07.
A1 101234567891234827SEP2012:10:36:3027SEP2012:10:36:30.
A1 101234567891234906SEP2012:11:17:5306SEP2012:11:17:53.
A1 201234567891235010SEP2012:16:07:5310SEP2012:16:07:53.
A1 201234567891235111SEP2012:12:32:3711SEP2012:12:32:37.
A1 101234567891235226SEP2012:14:48:1926SEP2012:14:48:19.
A1 101234567891235312SEP2012:17:28:0812SEP2012:17:28:08.
A1 101234567891235412SEP2012:17:29:0512SEP2012:17:29:05.
A1 201234567891235526OCT2012:11:00:1626OCT2012:11:00:16.
A1 101234567891235606SEP2012:11:21:3106SEP2012:11:21:31.
A1 201234567891235718SEP2012:10:11:1918SEP2012:10:11:19.
A1 101234567891235807SEP2012:08:59:4607SEP2012:08:59:46.
A1 201234567891235920SEP2012:12:18:5020SEP2012:12:18:50.
A1 101234567891236025SEP2012:08:33:5225SEP2012:08:33:52.
A1 101234567891236119SEP2012:11:37:1919SEP2012:11:37:19.
A1 101234567891236225SEP2012:10:29:0525SEP2012:10:29:05.
A1 101234567891236306SEP2012:10:52:4506SEP2012:10:52:45.
A1 101234567891236406SEP2012:16:03:2906SEP2012:16:03:29.
A1 101234567891236505SEP2012:20:09:1105SEP2012:20:09:11.
A1 301234567891236607SEP2012:10:45:5907SEP2012:10:45:59.
A1 101234567891236707SEP2012:10:46:2307SEP2012:10:46:23.
A1 101234567891236806SEP2012:07:45:5606SEP2012:07:45:56.
A1 101234567891236911OCT2012:18:59:4011OCT2012:18:59:40.
A1 101234567891237014SEP2012:11:51:1314SEP2012:11:51:13.
A1 101234567891237111SEP2012:08:36:5211SEP2012:08:36:52.
A1 201234567891237214SEP2012:15:56:5114SEP2012:15:56:51.
A1 201234567891237314SEP2012:15:57:4414SEP2012:15:57:44.
A1 101234567891237414SEP2012:15:57:5414SEP2012:15:57:54.
A1 101234567891237514SEP2012:15:58:0314SEP2012:15:58:03.
A1 101234567891237606SEP2012:08:14:0306SEP2012:08:14:03.
A1 101234567891237706SEP2012:08:12:5206SEP2012:08:12:52.
A1 101234567891237812SEP2012:16:01:2912SEP2012:16:01:29.
A1 101234567891237902OCT2012:09:41:0502OCT2012:09:41:05.
A1 201234567891238021SEP2012:13:14:2621SEP2012:13:14:26.
A1 201234567891238112SEP2012:17:08:1812SEP2012:17:08:18.
A1 201234567891238217OCT2012:16:58:5417OCT2012:16:58:54.
A1 101234567891238317SEP2012:18:42:4917SEP2012:18:42:49.
A1 101234567891238417SEP2012:09:24:4217SEP2012:09:24:42.
A1 101234567891238527SEP2012:11:32:5827SEP2012:11:32:58.
A1 201234567891238605OCT2012:09:36:0005OCT2012:09:36:00.
A1 101234567891238705OCT2012:11:37:0505OCT2012:11:37:05.
A1 101234567891238827SEP2012:09:02:0127SEP2012:09:02:01.
A1 201234567891238920SEP2012:07:19:4220SEP2012:07:19:42.
A1 101234567891239020SEP2012:07:21:0320SEP2012:07:21:03.
A1 101234567891239120SEP2012:07:21:1420SEP2012:07:21:14.
A1 101234567891239220SEP2012:07:21:2420SEP2012:07:21:24.
A1 201234567891239326SEP2012:08:46:0726SEP2012:08:46:07.
A1 201234567891239426SEP2012:08:43:3026SEP2012:08:43:30.
A1 101234567891239521SEP2012:13:12:3821SEP2012:13:12:38.
A1 101234567891239621SEP2012:08:30:0121SEP2012:08:30:01.
A1 201234567891239727SEP2012:14:33:1627SEP2012:14:33:16.
A1 201234567891239825SEP2012:12:03:2425SEP2012:12:03:24.
A1 101234567891239912OCT2012:13:19:2312OCT2012:13:19:23.
A1 101234567891240020SEP2012:07:21:3820SEP2012:07:21:38.
A1 101234567891240124OCT2012:09:34:2924OCT2012:09:34:29.
A1 101234567891240224OCT2012:09:33:4824OCT2012:09:33:48.
A1 101234567891240324OCT2012:09:34:0824OCT2012:09:34:08.
A1 101234567891240404OCT2012:14:03:0204OCT2012:14:03:02.
A1 101234567891240501OCT2012:08:26:1301OCT2012:08:26:13.
A1 201234567891240631OCT2012:14:35:4831OCT2012:14:35:48.
A1 101234567891240724SEP2012:16:11:1124SEP2012:16:11:11.
A1 201234567891240812OCT2012:16:51:2412OCT2012:16:51:24.
A1 101234567891240925OCT2012:16:29:4725OCT2012:16:29:47.
A1 201234567891241027SEP2012:09:29:1827SEP2012:09:29:18.
A1 101234567891241123OCT2012:16:18:4023OCT2012:16:18:40.
A1 301234567891241211OCT2012:18:15:0411OCT2012:18:15:04.
A1 201234567891241318OCT2012:18:01:0218OCT2012:18:01:02.
A1 101234567891241402OCT2012:10:34:0102OCT2012:10:34:01.
A1 201234567891241527SEP2012:11:59:3827SEP2012:11:59:38.
A1 101234567891241628SEP2012:16:15:2628SEP2012:16:15:26.
A1 101234567891241726OCT2012:08:51:1226OCT2012:08:51:12.
A1 101234567891241821SEP2012:18:48:2821SEP2012:18:48:28.
A1 201234567891241901OCT2012:09:34:5801OCT2012:09:34:58.
A1 101234567891242024SEP2012:16:07:5424SEP2012:16:07:54.
A1 101234567891242131OCT2012:13:41:2531OCT2012:13:41:25.
A1 101234567891242208NOV2012:15:35:0308NOV2012:15:35:03.
A1 301234567891242312OCT2012:13:42:2212OCT2012:13:42:22.
A1 201234567891242411OCT2012:16:28:0211OCT2012:16:28:02.
A1 201234567891242502NOV2012:10:19:4102NOV2012:10:19:41.
A1 201234567891242625OCT2012:10:06:5925OCT2012:10:06:59.
A1 101234567891242725OCT2012:10:44:4225OCT2012:10:44:42.
A1 101234567891242808NOV2012:10:26:0308NOV2012:10:26:03.
A1 101234567891242905OCT2012:10:33:3405OCT2012:10:33:34.
A1 101234567891243011OCT2012:17:41:5211OCT2012:17:41:52.
A1 101234567891243128SEP2012:15:25:4528SEP2012:15:25:45.
A1 201234567891243218OCT2012:11:59:4118OCT2012:11:59:41.
A1 101234567891243324OCT2012:09:37:5624OCT2012:09:37:56.
A1 101234567891243402OCT2012:15:34:5202OCT2012:15:34:52.
A1 201234567891243518OCT2012:15:46:0618OCT2012:15:46:06.
A1 101234567891243625OCT2012:10:02:5325OCT2012:10:02:53.
A1 101234567891243716OCT2012:13:23:2116OCT2012:13:23:21.
A1 101234567891243831OCT2012:13:50:2231OCT2012:13:50:22.
A1 101234567891243912OCT2012:14:30:1412OCT2012:14:30:14.
A1 101234567891244008NOV2012:10:26:0808NOV2012:10:26:08.
A1 101234567891244130OCT2012:17:41:1030OCT2012:17:41:10.
A1 201234567891244205OCT2012:11:01:2205OCT2012:11:01:22.
A1 201234567891244330OCT2012:16:45:2230OCT2012:16:45:22.
A1 101234567891244408NOV2012:10:24:1008NOV2012:10:24:10.
A1 101234567891244508NOV2012:10:24:2008NOV2012:10:24:20.
A1 101234567891244624OCT2012:09:35:0724OCT2012:09:35:07.
A1 201234567891244722OCT2012:11:18:0722OCT2012:11:18:07.
A1 301234567891244823OCT2012:13:55:4123OCT2012:13:55:41.
A1 101234567891244902NOV2012:11:32:1102NOV2012:11:32:11.
A1 101234567891245002NOV2012:11:32:1902NOV2012:11:32:19.
A1 201234567891245125OCT2012:15:39:0925OCT2012:15:39:09.
A1 201234567891245205NOV2012:11:37:2905NOV2012:11:37:29.
A1 101234567891245326OCT2012:16:02:5726OCT2012:16:02:57.
A1 201234567891245422OCT2012:12:06:4722OCT2012:12:06:47.
A1 101234567891245525OCT2012:16:38:5825OCT2012:16:38:58.
A1 101234567891245608NOV2012:11:02:2908NOV2012:11:02:29.
A1 201234567891245725OCT2012:12:31:4925OCT2012:12:31:49.
A1 201234567891245825OCT2012:09:43:5725OCT2012:09:43:57.
A1 201234567891245925OCT2012:09:45:0025OCT2012:09:45:00.
A1 101234567891246018OCT2012:11:50:4418OCT2012:11:50:44.
A1 101234567891246124OCT2012:09:38:5224OCT2012:09:38:52.
A1 201234567891246224OCT2012:10:54:4824OCT2012:10:54:48.
A1 101234567891246302NOV2012:11:32:2902NOV2012:11:32:29.
A1 201234567891246407NOV2012:12:35:5007NOV2012:12:35:50.
A1 101234567891246501NOV2012:12:34:5501NOV2012:12:34:55.
A1 201234567891246624OCT2012:12:03:0724OCT2012:12:03:07.
A1 101234567891246707NOV2012:16:50:5407NOV2012:16:50:54.
A1 101234567891246825OCT2012:10:05:2325OCT2012:10:05:23.
A1 101234567891246925OCT2012:17:28:1325OCT2012:17:28:13.
A1 201234567891247019OCT2012:09:30:5019OCT2012:09:30:50.
A1 101234567891247124OCT2012:15:55:2124OCT2012:15:55:21.
A1 101234567891247230OCT2012:17:15:3130OCT2012:17:15:31.
A1 301234567891247330OCT2012:16:59:4730OCT2012:16:59:47.
A1 201234567891247425OCT2012:08:55:3525OCT2012:08:55:35.
A1 101234567891247502NOV2012:15:59:2702NOV2012:15:59:27.
A1 101234567891247602NOV2012:15:59:4402NOV2012:15:59:44.
A1 101234567891247702NOV2012:15:59:5102NOV2012:15:59:51.
A1 101234567891247831OCT2012:10:10:2631OCT2012:10:10:26.
A1 101234567891247931OCT2012:14:50:2331OCT2012:14:50:23.
A1 201234567891248001NOV2012:15:16:3401NOV2012:15:16:34.
A1 101234567891248109NOV2012:17:59:4709NOV2012:17:59:47.
A1 201234567891248208NOV2012:10:24:3008NOV2012: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?

Attachment
Esteemed Advisor
Posts: 6,877

Re: IF THEN/ELSE help

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.

Grand Advisor
Posts: 9,696

Re: IF THEN/ELSE help

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;

Grand Advisor
Posts: 16,281

Re: IF THEN/ELSE help


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;

Contributor
Posts: 61

Re: IF THEN/ELSE help

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)Smiley SadColumn).

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.

Contributor
Posts: 61

Re: IF THEN/ELSE help

Sorry, for the incomplete message.

Line 11155 column 1 is this line in your code.

cnts(code)+1;

Grand Advisor
Posts: 16,281

Re: IF THEN/ELSE help

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.

Esteemed Advisor
Posts: 6,877

Re: IF THEN/ELSE help

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;

Post a Question
Discussion Stats
  • 7 replies
  • 310 views
  • 5 likes
  • 4 in conversation