BookmarkSubscribeRSS Feed
chandler
Fluorite | Level 6

    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?

7 REPLIES 7
art297
Opal | Level 21

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
Super User

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
Super User


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
Fluorite | Level 6

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
Fluorite | Level 6

Sorry, for the incomplete message.

Line 11155 column 1 is this line in your code.

cnts(code)+1;

Reeza
Super User

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
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1249 views
  • 5 likes
  • 4 in conversation