Solved: Re: Arrays - Page 2

vnreddy · Posted 12-03-2019 06:18 AM

I tried the program given by you..i am not getting any results in Month column. I am getting blank rows

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         
25         data want;
26         set nemo_ecl_prod;
27         by OriginalApplicationID;
28         retain
29           month2
30           month3
31         ;
32         if first.OriginalApplicationID
33         then do;
34           month2 = .;
35           month3 = .;
36           month = .;
37         end;
38         if not last.OriginalApplicationID
39         then do;
40           if month2 = . and stage = 2 then month2 = month(nemo_date);
41           if month3 = . and stage = 3 then month3 = month(nemo_date);
42         end;
43         if last.OriginalApplicationID;
44         if stage = 1 and month2 ne . then month = put(mdy(month2,1,2019),monname.);
45         if stage = 2 and month3 ne . then month = put(mdy(month3,1,2019),monname.);
46         keep OriginalApplicationID month;
47         run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      44:43   45:43   
NOTE: Invalid numeric data, '    April' , at line 45 column 43.
OriginalApplicationID=7267 Stage=2 ECL=656.0290522 Nemo_date=2019-10-31 run_date=2019-11-04 FIRST.OriginalApplicationID=0
LAST.OriginalApplicationID=1 month2=12 month3=4 month=. _ERROR_=1 _N_=11
NOTE: Invalid numeric data, '    March' , at line 44 column 43.
OriginalApplicationID=7793 Stage=1 ECL=17.12617064 Nemo_date=2019-10-31 run_date=2019-11-04 FIRST.OriginalApplicationID=0
LAST.OriginalApplicationID=1 month2=3 month3=. month=. _ERROR_=1 _N_=22
2                                                          The SAS System                            10:25 Tuesday, December 3, 2019

NOTE: Invalid numeric data, '   August' , at line 44 column 43.
OriginalApplicationID=9434 Stage=1 ECL=35.51067285 Nemo_date=2019-10-31 run_date=2019-11-04 FIRST.OriginalApplicationID=0
LAST.OriginalApplicationID=1 month2=8 month3=. month=. _ERROR_=1 _N_=33
NOTE: Invalid numeric data, ' December' , at line 44 column 43.
OriginalApplicationID=9532 Stage=1 ECL=186.1166642 Nemo_date=2019-10-31 run_date=2019-11-04 FIRST.OriginalApplicationID=0
LAST.OriginalApplicationID=1 month2=12 month3=. month=. _ERROR_=1 _N_=55
NOTE: There were 55 observations read from the data set WORK.NEMO_ECL_PROD.
NOTE: The data set WORK.WANT has 5 observations and 2 variables.

Kurt_Bremser · Posted 12-03-2019 06:38 AM

Remove the line

  month = .;

from the code (line 36 in your log). That's an artifact from testing it with purely numeric values.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

vnreddy · Posted 12-03-2019 06:48 AM

Thank you so much

vnreddy · Posted 12-03-2019 10:48 AM

Hi @Kurt_Bremser

program what you gave yesterday worked fine with the data what i have yesterday, unfortunately, it's not working

with another piece of data with different stages.

Input

OriginalApplicationID November December January February March April May June July August September October
113 2 2 2 2 2 1 1 1 1 1 1 1
767 2 2 2 2 2 2 1 1 1 2 1 1
1606 2 2 2 1 1 1 1 1 1 1 1 1
4745 1 1 1 1 1 1 2 1 1 1 1 1
4750 2 2 2 2 2 2 2 2 2 2 1 1
4769 3 3 3 3 3 3 3 3 3 3 3 2
4964 2 2 2 2 2 2 2 1 1 1 2 1
5585 1 1 1 2 2 1 1 1 1 1 1 1
5601 2 2 2 2 2 2 2 1 1 1 1 1
5608 1 1 2 2 2 1 1 1 1 1 1 1
6993 1 1 2 2 2 1 1 1 1 1 1 1
7267 2 2 2 2 2 3 3 3 3 3 2 2
7793 1 1 1 1 2 2 2 1 1 1 1 1
8160 3 2 2 2 2 2 2 2 2 2 2 2
8691 1 1 1 1 2 2 2 2 2 1 1 1
8758 1 1 1 1 1 2 2 2 1 1 1 1
253 2 2 2 2 2 2 2 2 2 2 2 2
254 2 2 2 2 2 2 2 2 2 2 2 2
424 2 2 2 2 2 2 2 2 2 2 2 2
467 1 1 1 1 1 1 1 1 1 1 1 1
509 1 1 1 1 1 1 1 1 1 1 1 1
585 1 1 1 1 1 1 1 1 1 1 1 1
618 3 3 3 3 3 3 3 3 3 3 3 3
646 1 1 1 1 1 1 1 1 1 1 1 1
654 1 1 1 1 1 1 1 1 1 1 1 1

Expected output
OriginalApplicationID November December January February March April May June July August September October Default
113 2 2 2 2 2 1 1 1 1 1 1 1 November
767 2 2 2 2 2 2 1 1 1 2 1 1 November
1606 2 2 2 1 1 1 1 1 1 1 1 1 November
4745 1 1 1 1 1 1 2 1 1 1 1 1 May
4750 2 2 2 2 2 2 2 2 2 2 1 1 November
4769 3 3 3 3 3 3 3 3 3 3 3 2 November
4964 2 2 2 2 2 2 2 1 1 1 2 1 November
5585 1 1 1 2 2 1 1 1 1 1 1 1 February
5601 2 2 2 2 2 2 2 1 1 1 1 1 November
5608 1 1 2 2 2 1 1 1 1 1 1 1 January
6993 1 1 2 2 2 1 1 1 1 1 1 1 January
7267 2 2 2 2 2 3 3 3 3 3 2 2 April
7793 1 1 1 1 2 2 2 1 1 1 1 1 March
8160 3 2 2 2 2 2 2 2 2 2 2 2 November
8691 1 1 1 1 2 2 2 2 2 1 1 1 March
8758 1 1 1 1 1 2 2 2 1 1 1 1 April
253 2 2 2 2 2 2 2 2 2 2 2 2
254 2 2 2 2 2 2 2 2 2 2 2 2
424 2 2 2 2 2 2 2 2 2 2 2 2
467 1 1 1 1 1 1 1 1 1 1 1 1
509 1 1 1 1 1 1 1 1 1 1 1 1
585 1 1 1 1 1 1 1 1 1 1 1 1
618 3 3 3 3 3 3 3 3 3 3 3 3
646 1 1 1 1 1 1 1 1 1 1 1 1
654 1 1 1 1 1 1 1 1 1 1 1 1

ballardw · Posted 12-04-2019 06:40 PM

@vnreddy wrote:

Hi @Kurt_Bremser

program what you gave yesterday worked fine with the data what i have yesterday, unfortunately, it's not working

with another piece of data with different stages.

Input

OriginalApplicationID November December January February March April May June July August September October
113 2 2 2 2 2 1 1 1 1 1 1 1
767 2 2 2 2 2 2 1 1 1 2 1 1
1606 2 2 2 1 1 1 1 1 1 1 1 1
4745 1 1 1 1 1 1 2 1 1 1 1 1
4750 2 2 2 2 2 2 2 2 2 2 1 1
4769 3 3 3 3 3 3 3 3 3 3 3 2
4964 2 2 2 2 2 2 2 1 1 1 2 1
5585 1 1 1 2 2 1 1 1 1 1 1 1
5601 2 2 2 2 2 2 2 1 1 1 1 1
5608 1 1 2 2 2 1 1 1 1 1 1 1
6993 1 1 2 2 2 1 1 1 1 1 1 1
7267 2 2 2 2 2 3 3 3 3 3 2 2
7793 1 1 1 1 2 2 2 1 1 1 1 1
8160 3 2 2 2 2 2 2 2 2 2 2 2
8691 1 1 1 1 2 2 2 2 2 1 1 1
8758 1 1 1 1 1 2 2 2 1 1 1 1
253 2 2 2 2 2 2 2 2 2 2 2 2
254 2 2 2 2 2 2 2 2 2 2 2 2
424 2 2 2 2 2 2 2 2 2 2 2 2
467 1 1 1 1 1 1 1 1 1 1 1 1
509 1 1 1 1 1 1 1 1 1 1 1 1
585 1 1 1 1 1 1 1 1 1 1 1 1
618 3 3 3 3 3 3 3 3 3 3 3 3
646 1 1 1 1 1 1 1 1 1 1 1 1
654 1 1 1 1 1 1 1 1 1 1 1 1

Expected output
OriginalApplicationID November December January February March April May June July August September October Default
113 2 2 2 2 2 1 1 1 1 1 1 1 November
767 2 2 2 2 2 2 1 1 1 2 1 1 November
1606 2 2 2 1 1 1 1 1 1 1 1 1 November
4745 1 1 1 1 1 1 2 1 1 1 1 1 May
4750 2 2 2 2 2 2 2 2 2 2 1 1 November
4769 3 3 3 3 3 3 3 3 3 3 3 2 November
4964 2 2 2 2 2 2 2 1 1 1 2 1 November
5585 1 1 1 2 2 1 1 1 1 1 1 1 February
5601 2 2 2 2 2 2 2 1 1 1 1 1 November
5608 1 1 2 2 2 1 1 1 1 1 1 1 January
6993 1 1 2 2 2 1 1 1 1 1 1 1 January
7267 2 2 2 2 2 3 3 3 3 3 2 2 April
7793 1 1 1 1 2 2 2 1 1 1 1 1 March
8160 3 2 2 2 2 2 2 2 2 2 2 2 November
8691 1 1 1 1 2 2 2 2 2 1 1 1 March
8758 1 1 1 1 1 2 2 2 1 1 1 1 April
253 2 2 2 2 2 2 2 2 2 2 2 2
254 2 2 2 2 2 2 2 2 2 2 2 2
424 2 2 2 2 2 2 2 2 2 2 2 2
467 1 1 1 1 1 1 1 1 1 1 1 1
509 1 1 1 1 1 1 1 1 1 1 1 1
585 1 1 1 1 1 1 1 1 1 1 1 1
618 3 3 3 3 3 3 3 3 3 3 3 3
646 1 1 1 1 1 1 1 1 1 1 1 1
654 1 1 1 1 1 1 1 1 1 1 1 1

Please note that the code that @Kurt_Bremser used starts with a data set using:

input OriginalApplicationID Stage ECL Nemo_date :date9. run_date :date9.;

Your above data example apparently has variables:

OriginalApplicationID November December January February March April May June July August September October

Since the number and types of variables have almost no match I would be very surprised if the code ran on other types of data.

Please note that Kurt's data has 2 date varaibles and your last example has no date variables. You would have to transform your data to look like the other data set to have a chance of getting the code to run.

If you are referring to this part of Kurt's code:

data nemo_test;
/*set nemo_stg_lmth;*/
set nemo_m_fmt;
array mth(11) December January February March April May June July August September October;
/*array mth(11) &dt_nm;*/
if mth(11) = 3 then
Month = " ";
else if mth(11) = 2 then
do;
  do i = 1 to 10;
    if mth(i) = 3 then
    do;
      Month = i;
      leave;
    end;
  end;
end;
else;
if mth(11) = 1 then
do;
  do i = 1 to 10;
    if mth(i) = 3 then
    do;
      Month = i;
      leave;
    end;
  end;
  if Month = "" then 
  do;
    do i = 1 to 10;
      if mth(i) = 2 then
      do;
        Month = i;
        leave;
      end;
    end;
  end;
end;
if Month NE "" then
S_Month = vname(mth(Month));
run;

Then you need to note that your are including MORE variables (November), the order would change since where December was first position it is now second.

So please show the actual code that you attempted against the new "data" that includes all of the adjustments you needed to make to address the additional variable and the changed positions of the like named data.

Kurt_Bremser · Posted 12-05-2019 01:51 AM

My program works with an intelligent ("long") data structure, so you have to work from that. "Wide" data is always hard to work with.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

vnreddy · Posted 12-05-2019 06:26 AM

Hi @Kurt_Bremser and @ballardw

thank you for the support you are giving;

@Kurt_Bremser i do completely agree with you, but it's my mistake in conveying the business rule.

here is the complete requirement and additional source data:

Source dataset we have ID stage ECL Nemo_Date run_date
Target dataset we need ID December January February March April May June July August September October November MONTH DEC18 JAN19 FEB19 MAR19 APR19 MAY19 JUN19 JUL19 AUG19 SEP19 OCT19 NOV19 REVISED OVERLAY
target dataset example:

ID December January February March April May June July August September October November Month DEC18 JAN19 FEB19 MAR19 APR19 MAY19 JUN19 JUL19 AUG19 SEP19 OCT19 NOV19 REVISED OVERLAY
1606 2 2 1 1 1 1 1 1 1 1 1 2 December 2,105 1,529 76 75 75 75 75 75 74 73 73 2,322 2,105 2,032
980 3 3 3 3 3 3 3 3 3 3 3 3 16,115 17,283 17,712 18,291 17,072 17,010 19,799 17,633 17,538 16,441 15,665 15,590

*) if last month stage is missing then that id has to be excluded from report
*) MONTH business rule: Stage: If last month is 3 then Month should be NULL
then search stage 2 from last month in mon1 to mon11 for stage 3 if true month name else NULL
then search stage 1 from last month in mon1 to mon11 for stage 3,2 if ture month name else NULL
*) revised ecl amount should be populated where Month is available in Month column
for example: if December Month in MONTH COLUMN then December ecl amount should be captured in Revised column (as shown above)
*) overlay column also depends on revised, if amount in revised column then revised - last month amount (as shown above)

*) want the data from long to wide format
*) Month names is mandatory for business, it's easy for the business to see which month ID has a change

ID Stage ECL Nemo_date run_date
113 2 20.46635874 31DEC2018 03JAN19:06:10:18
253 2 151.89921902 31DEC2018 03JAN19:06:10:18
254 2 752.1434742 31DEC2018 03JAN19:06:10:18
424 2 381.95786399 31DEC2018 03JAN19:06:10:18
467 1 54.242825626 31DEC2018 03JAN19:06:10:18
509 1 140.80432621 31DEC2018 03JAN19:06:10:18
585 1 46.757903196 31DEC2018 03JAN19:06:10:18
618 3 17838.516473 31DEC2018 03JAN19:06:10:18
646 1 1.4027319654 31DEC2018 03JAN19:06:10:18
654 1 1.9729897596 31DEC2018 03JAN19:06:10:18
767 2 937.15402091 31DEC2018 03JAN19:06:10:18
1606 2 2104.617633 31DEC2018 03JAN19:06:10:18
4745 1 45.034218874 31DEC2018 03JAN19:06:10:18
4750 2 1446.589353 31DEC2018 03JAN19:06:10:18
4769 3 646.00385791 31DEC2018 03JAN19:06:10:18
4964 2 2474.8923119 31DEC2018 03JAN19:06:10:18
5585 1 62.472567841 31DEC2018 03JAN19:06:10:18
5601 2 23.422928663 31DEC2018 03JAN19:06:10:18
5608 1 7.3550760663 31DEC2018 03JAN19:06:10:18
6993 1 11.525864308 31DEC2018 03JAN19:06:10:18
7267 2 310.49225779 31DEC2018 03JAN19:06:10:18
7793 1 35.583162036 31DEC2018 03JAN19:06:10:18
8160 2 721.89526909 31DEC2018 03JAN19:06:10:18
8691 1 30.852079096 31DEC2018 03JAN19:06:10:18
8758 1 65.400469942 31DEC2018 03JAN19:06:10:18
113 2 240.49996134 31JAN2019 04FEB19:06:04:44
253 2 155.11089521 31JAN2019 04FEB19:06:04:44
254 2 721.96348394 31JAN2019 04FEB19:06:04:44
424 2 341.78684957 31JAN2019 04FEB19:06:04:44
467 1 54.550273923 31JAN2019 04FEB19:06:04:44
509 1 124.59797166 31JAN2019 04FEB19:06:04:44
585 1 44.10680372 31JAN2019 04FEB19:06:04:44
618 3 17679.125664 31JAN2019 04FEB19:06:04:44
646 1 1.3698129378 31JAN2019 04FEB19:06:04:44
654 1 1.949665947 31JAN2019 04FEB19:06:04:44
767 2 948.03214347 31JAN2019 04FEB19:06:04:44
1606 2 1528.6456431 31JAN2019 04FEB19:06:04:44
4745 1 45.086359956 31JAN2019 04FEB19:06:04:44
4750 2 1446.3113241 31JAN2019 04FEB19:06:04:44
4769 3 480.34155366 31JAN2019 04FEB19:06:04:44
4964 2 532.11561229 31JAN2019 04FEB19:06:04:44
5585 1 62.708936525 31JAN2019 04FEB19:06:04:44
5601 2 23.093829755 31JAN2019 04FEB19:06:04:44
5608 2 35.909272836 31JAN2019 04FEB19:06:04:44
6993 2 129.07495746 31JAN2019 04FEB19:06:04:44
7267 2 284.17950282 31JAN2019 04FEB19:06:04:44
7793 1 35.969572076 31JAN2019 04FEB19:06:04:44
8160 2 698.32435668 31JAN2019 04FEB19:06:04:44
8691 1 30.875200259 31JAN2019 04FEB19:06:04:44
8758 1 47.969477628 31JAN2019 04FEB19:06:04:44
113 2 16.17325128 28FEB2019 04MAR19:10:55:37
253 2 155.02485146 28FEB2019 04MAR19:10:55:37
254 2 698.32046477 28FEB2019 04MAR19:10:55:37
424 2 324.54996467 28FEB2019 04MAR19:10:55:37
467 1 54.117136661 28FEB2019 04MAR19:10:55:37
509 1 210.55762424 28FEB2019 04MAR19:10:55:37
585 1 43.655356565 28FEB2019 04MAR19:10:55:37
618 3 17801.735267 28FEB2019 04MAR19:10:55:37
646 1 1.3367078621 28FEB2019 04MAR19:10:55:37
654 1 1.9274254292 28FEB2019 04MAR19:10:55:37
767 2 951.17844566 28FEB2019 04MAR19:10:55:37
1606 1 75.505580522 28FEB2019 04MAR19:10:55:37
4745 1 45.089995284 28FEB2019 04MAR19:10:55:37
4750 2 1367.4504408 28FEB2019 04MAR19:10:55:37
4769 3 440.34153363 28FEB2019 04MAR19:10:55:37
4964 2 503.74666827 28FEB2019 04MAR19:10:55:37
5585 2 1110.0488989 28FEB2019 04MAR19:10:55:37
5601 2 21.868959551 28FEB2019 04MAR19:10:55:37
5608 2 34.426697678 28FEB2019 04MAR19:10:55:37
6993 2 127.30398963 28FEB2019 04MAR19:10:55:37
7267 2 305.92524322 28FEB2019 04MAR19:10:55:37
7793 1 55.378558197 28FEB2019 04MAR19:10:55:37
8160 2 580.95872292 28FEB2019 04MAR19:10:55:37
8691 1 30.551538243 28FEB2019 04MAR19:10:55:37
8758 1 47.389386568 28FEB2019 04MAR19:10:55:37
113 2 15.455339675 31MAR2019 02APR19:09:55:12
253 2 153.6376364 31MAR2019 02APR19:09:55:12
254 2 436.75464466 31MAR2019 02APR19:09:55:12
424 2 328.88999593 31MAR2019 02APR19:09:55:12
467 1 54.06840136 31MAR2019 02APR19:09:55:12
509 1 209.68873825 31MAR2019 02APR19:09:55:12
585 1 48.262906991 31MAR2019 02APR19:09:55:12
618 3 18545.164686 31MAR2019 02APR19:09:55:12
646 1 1.3026264259 31MAR2019 02APR19:09:55:12
654 1 1.9037712007 31MAR2019 02APR19:09:55:12
767 2 942.91600294 31MAR2019 02APR19:09:55:12
1606 1 75.221661897 31MAR2019 02APR19:09:55:12
4745 1 44.63536115 31MAR2019 02APR19:09:55:12
4750 2 1324.9215109 31MAR2019 02APR19:09:55:12
4769 3 400.54048913 31MAR2019 02APR19:09:55:12
4964 2 463.90847202 31MAR2019 02APR19:09:55:12
5585 2 1090.61798 31MAR2019 02APR19:09:55:12
5601 2 21.538505057 31MAR2019 02APR19:09:55:12
5608 2 30.623114891 31MAR2019 02APR19:09:55:12
6993 2 127.83681755 31MAR2019 02APR19:09:55:12
7267 2 647.556714 31MAR2019 02APR19:09:55:12
7793 2 923.28376445 31MAR2019 02APR19:09:55:12
8160 2 571.94510683 31MAR2019 02APR19:09:55:12
8691 2 338.56100037 31MAR2019 02APR19:09:55:12
8758 1 54.048513995 31MAR2019 02APR19:09:55:12
113 1 4.0449254499 30APR2019 02MAY19:06:46:31
253 2 152.8092591 30APR2019 02MAY19:06:46:31
254 2 418.04376465 30APR2019 02MAY19:06:46:31
424 2 192.50580059 30APR2019 02MAY19:06:46:31
467 1 54.564550877 30APR2019 02MAY19:06:46:31
509 1 139.52081795 30APR2019 02MAY19:06:46:31
585 1 45.55006243 30APR2019 02MAY19:06:46:31
618 3 18274.54589 30APR2019 02MAY19:06:46:31
646 1 1.2683902722 30APR2019 02MAY19:06:46:31
654 1 1.8789628501 30APR2019 02MAY19:06:46:31
767 2 950.30332959 30APR2019 02MAY19:06:46:31
1606 1 75.310122205 30APR2019 02MAY19:06:46:31
4745 1 44.196374976 30APR2019 02MAY19:06:46:31
4750 2 1408.754366 30APR2019 02MAY19:06:46:31
4769 3 359.25765399 30APR2019 02MAY19:06:46:31
4964 2 464.86078609 30APR2019 02MAY19:06:46:31
5585 1 69.504351582 30APR2019 02MAY19:06:46:31
5601 2 21.213528117 30APR2019 02MAY19:06:46:31
5608 1 6.1912479615 30APR2019 02MAY19:06:46:31
6993 1 9.4697554824 30APR2019 02MAY19:06:46:31
7267 3 932.42841018 30APR2019 02MAY19:06:46:31
7793 2 919.04027491 30APR2019 02MAY19:06:46:31
8160 2 543.09012667 30APR2019 02MAY19:06:46:31
8691 2 346.17309912 30APR2019 02MAY19:06:46:31
8758 2 2110.6137662 30APR2019 02MAY19:06:46:31
113 1 3.8470308639 31MAY2019 04JUN19:06:05:27
253 2 152.24488404 31MAY2019 04JUN19:06:05:27
254 2 96.472817121 31MAY2019 04JUN19:06:05:27
424 2 181.58456792 31MAY2019 04JUN19:06:05:27
467 1 54.511114679 31MAY2019 04JUN19:06:05:27
509 1 140.29130872 31MAY2019 04JUN19:06:05:27
585 1 45.363627847 31MAY2019 04JUN19:06:05:27
618 3 18320.62846 31MAY2019 04JUN19:06:05:27
646 1 1.2343272924 31MAY2019 04JUN19:06:05:27
654 1 0.8367411553 31MAY2019 04JUN19:06:05:27
767 1 57.802943883 31MAY2019 04JUN19:06:05:27
1606 1 75.240692902 31MAY2019 04JUN19:06:05:27
4745 2 340.07494723 31MAY2019 04JUN19:06:05:27
4750 2 1432.1627042 31MAY2019 04JUN19:06:05:27
4769 3 319.62745817 31MAY2019 04JUN19:06:05:27
4964 2 80.042090186 31MAY2019 04JUN19:06:05:27
5585 1 70.153092208 31MAY2019 04JUN19:06:05:27
5601 2 19.893539384 31MAY2019 04JUN19:06:05:27
5608 1 5.8871740643 31MAY2019 04JUN19:06:05:27
6993 1 9.4764960671 31MAY2019 04JUN19:06:05:27
7267 3 839.53572683 31MAY2019 04JUN19:06:05:27
7793 2 930.65908395 31MAY2019 04JUN19:06:05:27
8160 2 317.48985802 31MAY2019 04JUN19:06:05:27
8691 2 339.47107599 31MAY2019 04JUN19:06:05:27
8758 2 2095.9581339 31MAY2019 04JUN19:06:05:27
113 1 3.6418345703 30JUN2019 02JUL19:06:05:38
253 2 160.12555809 30JUN2019 02JUL19:06:05:38
254 2 61.848902557 30JUN2019 02JUL19:06:05:38
424 2 26.761118389 30JUN2019 02JUL19:06:05:38
467 1 53.970798759 30JUN2019 02JUL19:06:05:38
509 1 187.45534321 30JUN2019 02JUL19:06:05:38
585 1 54.725424189 30JUN2019 02JUL19:06:05:38
618 3 20168.828415 30JUN2019 02JUL19:06:05:38
646 1 1.1992606443 30JUN2019 02JUL19:06:05:38
654 1 0.825480982 30JUN2019 02JUL19:06:05:38
767 1 57.680725363 30JUN2019 02JUL19:06:05:38
1606 1 74.822719867 30JUN2019 02JUL19:06:05:38
4745 1 44.711332938 30JUN2019 02JUL19:06:05:38
4750 2 1464.3554442 30JUN2019 02JUL19:06:05:38
4769 3 278.45015971 30JUN2019 02JUL19:06:05:38
4964 1 26.490212655 30JUN2019 02JUL19:06:05:38
5585 1 69.711452467 30JUN2019 02JUL19:06:05:38
5601 1 8.7862523847 30JUN2019 02JUL19:06:05:38
5608 1 5.593929793 30JUN2019 02JUL19:06:05:38
6993 1 9.7424458438 30JUN2019 02JUL19:06:05:38
7267 3 806.460993 30JUN2019 02JUL19:06:05:38
7793 1 34.881156703 30JUN2019 02JUL19:06:05:38
8160 2 294.48216574 30JUN2019 02JUL19:06:05:38
8691 2 403.99829041 30JUN2019 02JUL19:06:05:38
8758 2 1803.742407 30JUN2019 02JUL19:06:05:38
113 1 2.6549385876 31JUL2019 02AUG19:16:50:19
253 2 157.42715684 31JUL2019 02AUG19:16:50:19
254 2 61.998629457 31JUL2019 02AUG19:16:50:19
424 2 25.775131808 31JUL2019 02AUG19:16:50:19
467 1 48.282332772 31JUL2019 02AUG19:16:50:19
509 1 194.36599843 31JUL2019 02AUG19:16:50:19
585 1 54.96598957 31JUL2019 02AUG19:16:50:19
618 3 20277.07267 31JUL2019 02AUG19:16:50:19
646 1 1.1660095295 31JUL2019 02AUG19:16:50:19
654 1 0.816212787 31JUL2019 02AUG19:16:50:19
767 1 58.813896642 31JUL2019 02AUG19:16:50:19
1606 1 74.760867439 31JUL2019 02AUG19:16:50:19
4745 1 45.038912917 31JUL2019 02AUG19:16:50:19
4750 2 1452.9679391 31JUL2019 02AUG19:16:50:19
4769 3 238.75411445 31JUL2019 02AUG19:16:50:19
4964 1 24.961828428 31JUL2019 02AUG19:16:50:19
5585 1 69.288600621 31JUL2019 02AUG19:16:50:19
5601 1 4.1644956019 31JUL2019 02AUG19:16:50:19
5608 1 5.2849642108 31JUL2019 02AUG19:16:50:19
6993 1 10.181067742 31JUL2019 02AUG19:16:50:19
7267 3 739.94688823 31JUL2019 02AUG19:16:50:19
7793 1 34.65632422 31JUL2019 02AUG19:16:50:19
8160 2 290.25039061 31JUL2019 02AUG19:16:50:19
8691 2 377.06214226 31JUL2019 02AUG19:16:50:19
8758 1 57.388223251 31JUL2019 02AUG19:16:50:19
113 1 2.498374817 31AUG2019 03SEP19:06:03:59
253 2 154.0248261 31AUG2019 03SEP19:06:03:59
254 2 58.437238802 31AUG2019 03SEP19:06:03:59
424 2 24.784432624 31AUG2019 03SEP19:06:03:59
467 1 47.583156548 31AUG2019 03SEP19:06:03:59
509 1 191.30969635 31AUG2019 03SEP19:06:03:59
585 1 54.229450445 31AUG2019 03SEP19:06:03:59
618 3 20157.98234 31AUG2019 03SEP19:06:03:59
646 1 0.8665982543 31AUG2019 03SEP19:06:03:59
654 1 0.615770443 31AUG2019 03SEP19:06:03:59
767 2 1097.0267945 31AUG2019 03SEP19:06:03:59
1606 1 74.03140867 31AUG2019 03SEP19:06:03:59
4745 1 44.304237099 31AUG2019 03SEP19:06:03:59
4750 2 1427.8077712 31AUG2019 03SEP19:06:03:59
4769 3 199.39725914 31AUG2019 03SEP19:06:03:59
4964 1 15.928808007 31AUG2019 03SEP19:06:03:59
5585 1 54.928719219 31AUG2019 03SEP19:06:03:59
5601 1 1.9629287156 31AUG2019 03SEP19:06:03:59
5608 1 4.9646328374 31AUG2019 03SEP19:06:03:59
6993 1 10.031212961 31AUG2019 03SEP19:06:03:59
7267 3 718.86098203 31AUG2019 03SEP19:06:03:59
7793 1 33.709684375 31AUG2019 03SEP19:06:03:59
8160 2 256.46773173 31AUG2019 03SEP19:06:03:59
8691 1 30.810728605 31AUG2019 03SEP19:06:03:59
8758 1 55.891715427 31AUG2019 03SEP19:06:03:59
113 1 2.3401166492 30SEP2019 02OCT19:06:04:18
253 2 149.71808117 30SEP2019 02OCT19:06:04:18
254 2 44.71783757 30SEP2019 02OCT19:06:04:18
424 2 18.43758187 30SEP2019 02OCT19:06:04:18
467 1 47.676676852 30SEP2019 02OCT19:06:04:18
509 1 179.53382655 30SEP2019 02OCT19:06:04:18
585 1 52.508030701 30SEP2019 02OCT19:06:04:18
618 3 19910.412559 30SEP2019 02OCT19:06:04:18
646 1 0.8397733242 30SEP2019 02OCT19:06:04:18
654 1 0.6076028241 30SEP2019 02OCT19:06:04:18
767 1 56.146690413 30SEP2019 02OCT19:06:04:18
1606 1 73.258886966 30SEP2019 02OCT19:06:04:18
4745 1 42.330887963 30SEP2019 02OCT19:06:04:18
4750 1 50.122647433 30SEP2019 02OCT19:06:04:18
4769 3 161.07842496 30SEP2019 02OCT19:06:04:18
4964 2 85.229406126 30SEP2019 02OCT19:06:04:18
5585 1 80.969716246 30SEP2019 02OCT19:06:04:18
5601 1 1.2955587086 30SEP2019 02OCT19:06:04:18
5608 1 4.6592669782 30SEP2019 02OCT19:06:04:18
6993 1 9.1523100492 30SEP2019 02OCT19:06:04:18
7267 2 635.57314495 30SEP2019 02OCT19:06:04:18
7793 1 33.075006361 30SEP2019 02OCT19:06:04:18
8160 2 234.84678153 30SEP2019 02OCT19:06:04:18
8691 1 28.405145457 30SEP2019 02OCT19:06:04:18
8758 1 47.392984462 30SEP2019 02OCT19:06:04:18
113 1 1.4934923608 31OCT2019 04NOV19:06:04:17
253 2 147.43531714 31OCT2019 04NOV19:06:04:17
254 2 29.85236368 31OCT2019 04NOV19:06:04:17
424 2 17.6225397 31OCT2019 04NOV19:06:04:17
467 1 47.509646815 31OCT2019 04NOV19:06:04:17
509 1 180.38860567 31OCT2019 04NOV19:06:04:17
585 1 51.819886666 31OCT2019 04NOV19:06:04:17
618 3 19906.248261 31OCT2019 04NOV19:06:04:17
646 1 0.8131099221 31OCT2019 04NOV19:06:04:17
654 1 0.5990375342 31OCT2019 04NOV19:06:04:17
767 1 55.752171141 31OCT2019 04NOV19:06:04:17
1606 1 72.703588822 31OCT2019 04NOV19:06:04:17
4745 1 41.822827446 31OCT2019 04NOV19:06:04:17
4750 1 49.4281835 31OCT2019 04NOV19:06:04:17
4769 2 121.30741193 31OCT2019 04NOV19:06:04:17
4964 1 22.321415208 31OCT2019 04NOV19:06:04:17
5585 1 36.881990209 31OCT2019 04NOV19:06:04:17
5601 1 1.272046705 31OCT2019 04NOV19:06:04:17
5608 1 4.3474306069 31OCT2019 04NOV19:06:04:17
6993 1 9.0944832076 31OCT2019 04NOV19:06:04:17
7267 2 656.02905222 31OCT2019 04NOV19:06:04:17
7793 1 17.126170637 31OCT2019 04NOV19:06:04:17
8160 2 236.30757553 31OCT2019 04NOV19:06:04:17
8691 1 28.30618198 31OCT2019 04NOV19:06:04:17
8758 1 54.684903158 31OCT2019 04NOV19:06:04:17
113 1 1.383013089 30NOV2019 03DEC19:09:04:37
253 2 147.83478255 30NOV2019 03DEC19:09:04:37
254 2 27.863723732 30NOV2019 03DEC19:09:04:37
424 2 16.831105784 30NOV2019 03DEC19:09:04:37
467 1 47.380406535 30NOV2019 03DEC19:09:04:37
509 1 206.10572211 30NOV2019 03DEC19:09:04:37
585 1 51.517863612 30NOV2019 03DEC19:09:04:37
618 3 19919.488195 30NOV2019 03DEC19:09:04:37
646 1 0.5350457603 30NOV2019 03DEC19:09:04:37
654 1 0.8911858103 30NOV2019 03DEC19:09:04:37
767 2 1129.7818647 30NOV2019 03DEC19:09:04:37
1606 2 2321.6419581 30NOV2019 03DEC19:09:04:37
4745 1 43.140229652 30NOV2019 03DEC19:09:04:37
4750 1 50.863402634 30NOV2019 03DEC19:09:04:37
4769 2 5.315273714 30NOV2019 03DEC19:09:04:37
4964 2 67.044439404 30NOV2019 03DEC19:09:04:37
5585 1 36.527428583 30NOV2019 03DEC19:09:04:37
5601 1 0.9539986273 30NOV2019 03DEC19:09:04:37
5608 1 4.0439109935 30NOV2019 03DEC19:09:04:37
6993 1 8.9655147898 30NOV2019 03DEC19:09:04:37
7267 2 620.49757145 30NOV2019 03DEC19:09:04:37
7793 1 16.896373249 30NOV2019 03DEC19:09:04:37
8160 2 220.08648564 30NOV2019 03DEC19:09:04:37
8691 1 27.894940809 30NOV2019 03DEC19:09:04:37
8758 1 54.371032858 30NOV2019 03DEC19:09:04:37

Kurt_Bremser · Posted 12-05-2019 07:45 AM

I've used a different approach, with a do loop to read the groups and a hash to store values:

data have;
input ID Stage ECL Nemo_date :date9. run_date :datetime16.;
format nemo_date e8601da10. run_date e8601dt19.;
month = put(nemo_date,monname.);
datalines;
113 2 20.46635874 31DEC2018 03JAN19:06:10:18
253 2 151.89921902 31DEC2018 03JAN19:06:10:18
254 2 752.1434742 31DEC2018 03JAN19:06:10:18
424 2 381.95786399 31DEC2018 03JAN19:06:10:18
467 1 54.242825626 31DEC2018 03JAN19:06:10:18
509 1 140.80432621 31DEC2018 03JAN19:06:10:18
585 1 46.757903196 31DEC2018 03JAN19:06:10:18
618 3 17838.516473 31DEC2018 03JAN19:06:10:18
646 1 1.4027319654 31DEC2018 03JAN19:06:10:18
654 1 1.9729897596 31DEC2018 03JAN19:06:10:18
767 2 937.15402091 31DEC2018 03JAN19:06:10:18
1606 2 2104.617633 31DEC2018 03JAN19:06:10:18
4745 1 45.034218874 31DEC2018 03JAN19:06:10:18
4750 2 1446.589353 31DEC2018 03JAN19:06:10:18
4769 3 646.00385791 31DEC2018 03JAN19:06:10:18
4964 2 2474.8923119 31DEC2018 03JAN19:06:10:18
5585 1 62.472567841 31DEC2018 03JAN19:06:10:18
5601 2 23.422928663 31DEC2018 03JAN19:06:10:18
5608 1 7.3550760663 31DEC2018 03JAN19:06:10:18
6993 1 11.525864308 31DEC2018 03JAN19:06:10:18
7267 2 310.49225779 31DEC2018 03JAN19:06:10:18
7793 1 35.583162036 31DEC2018 03JAN19:06:10:18
8160 2 721.89526909 31DEC2018 03JAN19:06:10:18
8691 1 30.852079096 31DEC2018 03JAN19:06:10:18
8758 1 65.400469942 31DEC2018 03JAN19:06:10:18
113 2 240.49996134 31JAN2019 04FEB19:06:04:44
253 2 155.11089521 31JAN2019 04FEB19:06:04:44
254 2 721.96348394 31JAN2019 04FEB19:06:04:44
424 2 341.78684957 31JAN2019 04FEB19:06:04:44
467 1 54.550273923 31JAN2019 04FEB19:06:04:44
509 1 124.59797166 31JAN2019 04FEB19:06:04:44
585 1 44.10680372 31JAN2019 04FEB19:06:04:44
618 3 17679.125664 31JAN2019 04FEB19:06:04:44
646 1 1.3698129378 31JAN2019 04FEB19:06:04:44
654 1 1.949665947 31JAN2019 04FEB19:06:04:44
767 2 948.03214347 31JAN2019 04FEB19:06:04:44
1606 2 1528.6456431 31JAN2019 04FEB19:06:04:44
4745 1 45.086359956 31JAN2019 04FEB19:06:04:44
4750 2 1446.3113241 31JAN2019 04FEB19:06:04:44
4769 3 480.34155366 31JAN2019 04FEB19:06:04:44
4964 2 532.11561229 31JAN2019 04FEB19:06:04:44
5585 1 62.708936525 31JAN2019 04FEB19:06:04:44
5601 2 23.093829755 31JAN2019 04FEB19:06:04:44
5608 2 35.909272836 31JAN2019 04FEB19:06:04:44
6993 2 129.07495746 31JAN2019 04FEB19:06:04:44
7267 2 284.17950282 31JAN2019 04FEB19:06:04:44
7793 1 35.969572076 31JAN2019 04FEB19:06:04:44
8160 2 698.32435668 31JAN2019 04FEB19:06:04:44
8691 1 30.875200259 31JAN2019 04FEB19:06:04:44
8758 1 47.969477628 31JAN2019 04FEB19:06:04:44
113 2 16.17325128 28FEB2019 04MAR19:10:55:37
253 2 155.02485146 28FEB2019 04MAR19:10:55:37
254 2 698.32046477 28FEB2019 04MAR19:10:55:37
424 2 324.54996467 28FEB2019 04MAR19:10:55:37
467 1 54.117136661 28FEB2019 04MAR19:10:55:37
509 1 210.55762424 28FEB2019 04MAR19:10:55:37
585 1 43.655356565 28FEB2019 04MAR19:10:55:37
618 3 17801.735267 28FEB2019 04MAR19:10:55:37
646 1 1.3367078621 28FEB2019 04MAR19:10:55:37
654 1 1.9274254292 28FEB2019 04MAR19:10:55:37
767 2 951.17844566 28FEB2019 04MAR19:10:55:37
1606 1 75.505580522 28FEB2019 04MAR19:10:55:37
4745 1 45.089995284 28FEB2019 04MAR19:10:55:37
4750 2 1367.4504408 28FEB2019 04MAR19:10:55:37
4769 3 440.34153363 28FEB2019 04MAR19:10:55:37
4964 2 503.74666827 28FEB2019 04MAR19:10:55:37
5585 2 1110.0488989 28FEB2019 04MAR19:10:55:37
5601 2 21.868959551 28FEB2019 04MAR19:10:55:37
5608 2 34.426697678 28FEB2019 04MAR19:10:55:37
6993 2 127.30398963 28FEB2019 04MAR19:10:55:37
7267 2 305.92524322 28FEB2019 04MAR19:10:55:37
7793 1 55.378558197 28FEB2019 04MAR19:10:55:37
8160 2 580.95872292 28FEB2019 04MAR19:10:55:37
8691 1 30.551538243 28FEB2019 04MAR19:10:55:37
8758 1 47.389386568 28FEB2019 04MAR19:10:55:37
113 2 15.455339675 31MAR2019 02APR19:09:55:12
253 2 153.6376364 31MAR2019 02APR19:09:55:12
254 2 436.75464466 31MAR2019 02APR19:09:55:12
424 2 328.88999593 31MAR2019 02APR19:09:55:12
467 1 54.06840136 31MAR2019 02APR19:09:55:12
509 1 209.68873825 31MAR2019 02APR19:09:55:12
585 1 48.262906991 31MAR2019 02APR19:09:55:12
618 3 18545.164686 31MAR2019 02APR19:09:55:12
646 1 1.3026264259 31MAR2019 02APR19:09:55:12
654 1 1.9037712007 31MAR2019 02APR19:09:55:12
767 2 942.91600294 31MAR2019 02APR19:09:55:12
1606 1 75.221661897 31MAR2019 02APR19:09:55:12
4745 1 44.63536115 31MAR2019 02APR19:09:55:12
4750 2 1324.9215109 31MAR2019 02APR19:09:55:12
4769 3 400.54048913 31MAR2019 02APR19:09:55:12
4964 2 463.90847202 31MAR2019 02APR19:09:55:12
5585 2 1090.61798 31MAR2019 02APR19:09:55:12
5601 2 21.538505057 31MAR2019 02APR19:09:55:12
5608 2 30.623114891 31MAR2019 02APR19:09:55:12
6993 2 127.83681755 31MAR2019 02APR19:09:55:12
7267 2 647.556714 31MAR2019 02APR19:09:55:12
7793 2 923.28376445 31MAR2019 02APR19:09:55:12
8160 2 571.94510683 31MAR2019 02APR19:09:55:12
8691 2 338.56100037 31MAR2019 02APR19:09:55:12
8758 1 54.048513995 31MAR2019 02APR19:09:55:12
113 1 4.0449254499 30APR2019 02MAY19:06:46:31
253 2 152.8092591 30APR2019 02MAY19:06:46:31
254 2 418.04376465 30APR2019 02MAY19:06:46:31
424 2 192.50580059 30APR2019 02MAY19:06:46:31
467 1 54.564550877 30APR2019 02MAY19:06:46:31
509 1 139.52081795 30APR2019 02MAY19:06:46:31
585 1 45.55006243 30APR2019 02MAY19:06:46:31
618 3 18274.54589 30APR2019 02MAY19:06:46:31
646 1 1.2683902722 30APR2019 02MAY19:06:46:31
654 1 1.8789628501 30APR2019 02MAY19:06:46:31
767 2 950.30332959 30APR2019 02MAY19:06:46:31
1606 1 75.310122205 30APR2019 02MAY19:06:46:31
4745 1 44.196374976 30APR2019 02MAY19:06:46:31
4750 2 1408.754366 30APR2019 02MAY19:06:46:31
4769 3 359.25765399 30APR2019 02MAY19:06:46:31
4964 2 464.86078609 30APR2019 02MAY19:06:46:31
5585 1 69.504351582 30APR2019 02MAY19:06:46:31
5601 2 21.213528117 30APR2019 02MAY19:06:46:31
5608 1 6.1912479615 30APR2019 02MAY19:06:46:31
6993 1 9.4697554824 30APR2019 02MAY19:06:46:31
7267 3 932.42841018 30APR2019 02MAY19:06:46:31
7793 2 919.04027491 30APR2019 02MAY19:06:46:31
8160 2 543.09012667 30APR2019 02MAY19:06:46:31
8691 2 346.17309912 30APR2019 02MAY19:06:46:31
8758 2 2110.6137662 30APR2019 02MAY19:06:46:31
113 1 3.8470308639 31MAY2019 04JUN19:06:05:27
253 2 152.24488404 31MAY2019 04JUN19:06:05:27
254 2 96.472817121 31MAY2019 04JUN19:06:05:27
424 2 181.58456792 31MAY2019 04JUN19:06:05:27
467 1 54.511114679 31MAY2019 04JUN19:06:05:27
509 1 140.29130872 31MAY2019 04JUN19:06:05:27
585 1 45.363627847 31MAY2019 04JUN19:06:05:27
618 3 18320.62846 31MAY2019 04JUN19:06:05:27
646 1 1.2343272924 31MAY2019 04JUN19:06:05:27
654 1 0.8367411553 31MAY2019 04JUN19:06:05:27
767 1 57.802943883 31MAY2019 04JUN19:06:05:27
1606 1 75.240692902 31MAY2019 04JUN19:06:05:27
4745 2 340.07494723 31MAY2019 04JUN19:06:05:27
4750 2 1432.1627042 31MAY2019 04JUN19:06:05:27
4769 3 319.62745817 31MAY2019 04JUN19:06:05:27
4964 2 80.042090186 31MAY2019 04JUN19:06:05:27
5585 1 70.153092208 31MAY2019 04JUN19:06:05:27
5601 2 19.893539384 31MAY2019 04JUN19:06:05:27
5608 1 5.8871740643 31MAY2019 04JUN19:06:05:27
6993 1 9.4764960671 31MAY2019 04JUN19:06:05:27
7267 3 839.53572683 31MAY2019 04JUN19:06:05:27
7793 2 930.65908395 31MAY2019 04JUN19:06:05:27
8160 2 317.48985802 31MAY2019 04JUN19:06:05:27
8691 2 339.47107599 31MAY2019 04JUN19:06:05:27
8758 2 2095.9581339 31MAY2019 04JUN19:06:05:27
113 1 3.6418345703 30JUN2019 02JUL19:06:05:38
253 2 160.12555809 30JUN2019 02JUL19:06:05:38
254 2 61.848902557 30JUN2019 02JUL19:06:05:38
424 2 26.761118389 30JUN2019 02JUL19:06:05:38
467 1 53.970798759 30JUN2019 02JUL19:06:05:38
509 1 187.45534321 30JUN2019 02JUL19:06:05:38
585 1 54.725424189 30JUN2019 02JUL19:06:05:38
618 3 20168.828415 30JUN2019 02JUL19:06:05:38
646 1 1.1992606443 30JUN2019 02JUL19:06:05:38
654 1 0.825480982 30JUN2019 02JUL19:06:05:38
767 1 57.680725363 30JUN2019 02JUL19:06:05:38
1606 1 74.822719867 30JUN2019 02JUL19:06:05:38
4745 1 44.711332938 30JUN2019 02JUL19:06:05:38
4750 2 1464.3554442 30JUN2019 02JUL19:06:05:38
4769 3 278.45015971 30JUN2019 02JUL19:06:05:38
4964 1 26.490212655 30JUN2019 02JUL19:06:05:38
5585 1 69.711452467 30JUN2019 02JUL19:06:05:38
5601 1 8.7862523847 30JUN2019 02JUL19:06:05:38
5608 1 5.593929793 30JUN2019 02JUL19:06:05:38
6993 1 9.7424458438 30JUN2019 02JUL19:06:05:38
7267 3 806.460993 30JUN2019 02JUL19:06:05:38
7793 1 34.881156703 30JUN2019 02JUL19:06:05:38
8160 2 294.48216574 30JUN2019 02JUL19:06:05:38
8691 2 403.99829041 30JUN2019 02JUL19:06:05:38
8758 2 1803.742407 30JUN2019 02JUL19:06:05:38
113 1 2.6549385876 31JUL2019 02AUG19:16:50:19
253 2 157.42715684 31JUL2019 02AUG19:16:50:19
254 2 61.998629457 31JUL2019 02AUG19:16:50:19
424 2 25.775131808 31JUL2019 02AUG19:16:50:19
467 1 48.282332772 31JUL2019 02AUG19:16:50:19
509 1 194.36599843 31JUL2019 02AUG19:16:50:19
585 1 54.96598957 31JUL2019 02AUG19:16:50:19
618 3 20277.07267 31JUL2019 02AUG19:16:50:19
646 1 1.1660095295 31JUL2019 02AUG19:16:50:19
654 1 0.816212787 31JUL2019 02AUG19:16:50:19
767 1 58.813896642 31JUL2019 02AUG19:16:50:19
1606 1 74.760867439 31JUL2019 02AUG19:16:50:19
4745 1 45.038912917 31JUL2019 02AUG19:16:50:19
4750 2 1452.9679391 31JUL2019 02AUG19:16:50:19
4769 3 238.75411445 31JUL2019 02AUG19:16:50:19
4964 1 24.961828428 31JUL2019 02AUG19:16:50:19
5585 1 69.288600621 31JUL2019 02AUG19:16:50:19
5601 1 4.1644956019 31JUL2019 02AUG19:16:50:19
5608 1 5.2849642108 31JUL2019 02AUG19:16:50:19
6993 1 10.181067742 31JUL2019 02AUG19:16:50:19
7267 3 739.94688823 31JUL2019 02AUG19:16:50:19
7793 1 34.65632422 31JUL2019 02AUG19:16:50:19
8160 2 290.25039061 31JUL2019 02AUG19:16:50:19
8691 2 377.06214226 31JUL2019 02AUG19:16:50:19
8758 1 57.388223251 31JUL2019 02AUG19:16:50:19
113 1 2.498374817 31AUG2019 03SEP19:06:03:59
253 2 154.0248261 31AUG2019 03SEP19:06:03:59
254 2 58.437238802 31AUG2019 03SEP19:06:03:59
424 2 24.784432624 31AUG2019 03SEP19:06:03:59
467 1 47.583156548 31AUG2019 03SEP19:06:03:59
509 1 191.30969635 31AUG2019 03SEP19:06:03:59
585 1 54.229450445 31AUG2019 03SEP19:06:03:59
618 3 20157.98234 31AUG2019 03SEP19:06:03:59
646 1 0.8665982543 31AUG2019 03SEP19:06:03:59
654 1 0.615770443 31AUG2019 03SEP19:06:03:59
767 2 1097.0267945 31AUG2019 03SEP19:06:03:59
1606 1 74.03140867 31AUG2019 03SEP19:06:03:59
4745 1 44.304237099 31AUG2019 03SEP19:06:03:59
4750 2 1427.8077712 31AUG2019 03SEP19:06:03:59
4769 3 199.39725914 31AUG2019 03SEP19:06:03:59
4964 1 15.928808007 31AUG2019 03SEP19:06:03:59
5585 1 54.928719219 31AUG2019 03SEP19:06:03:59
5601 1 1.9629287156 31AUG2019 03SEP19:06:03:59
5608 1 4.9646328374 31AUG2019 03SEP19:06:03:59
6993 1 10.031212961 31AUG2019 03SEP19:06:03:59
7267 3 718.86098203 31AUG2019 03SEP19:06:03:59
7793 1 33.709684375 31AUG2019 03SEP19:06:03:59
8160 2 256.46773173 31AUG2019 03SEP19:06:03:59
8691 1 30.810728605 31AUG2019 03SEP19:06:03:59
8758 1 55.891715427 31AUG2019 03SEP19:06:03:59
113 1 2.3401166492 30SEP2019 02OCT19:06:04:18
253 2 149.71808117 30SEP2019 02OCT19:06:04:18
254 2 44.71783757 30SEP2019 02OCT19:06:04:18
424 2 18.43758187 30SEP2019 02OCT19:06:04:18
467 1 47.676676852 30SEP2019 02OCT19:06:04:18
509 1 179.53382655 30SEP2019 02OCT19:06:04:18
585 1 52.508030701 30SEP2019 02OCT19:06:04:18
618 3 19910.412559 30SEP2019 02OCT19:06:04:18
646 1 0.8397733242 30SEP2019 02OCT19:06:04:18
654 1 0.6076028241 30SEP2019 02OCT19:06:04:18
767 1 56.146690413 30SEP2019 02OCT19:06:04:18
1606 1 73.258886966 30SEP2019 02OCT19:06:04:18
4745 1 42.330887963 30SEP2019 02OCT19:06:04:18
4750 1 50.122647433 30SEP2019 02OCT19:06:04:18
4769 3 161.07842496 30SEP2019 02OCT19:06:04:18
4964 2 85.229406126 30SEP2019 02OCT19:06:04:18
5585 1 80.969716246 30SEP2019 02OCT19:06:04:18
5601 1 1.2955587086 30SEP2019 02OCT19:06:04:18
5608 1 4.6592669782 30SEP2019 02OCT19:06:04:18
6993 1 9.1523100492 30SEP2019 02OCT19:06:04:18
7267 2 635.57314495 30SEP2019 02OCT19:06:04:18
7793 1 33.075006361 30SEP2019 02OCT19:06:04:18
8160 2 234.84678153 30SEP2019 02OCT19:06:04:18
8691 1 28.405145457 30SEP2019 02OCT19:06:04:18
8758 1 47.392984462 30SEP2019 02OCT19:06:04:18
113 1 1.4934923608 31OCT2019 04NOV19:06:04:17
253 2 147.43531714 31OCT2019 04NOV19:06:04:17
254 2 29.85236368 31OCT2019 04NOV19:06:04:17
424 2 17.6225397 31OCT2019 04NOV19:06:04:17
467 1 47.509646815 31OCT2019 04NOV19:06:04:17
509 1 180.38860567 31OCT2019 04NOV19:06:04:17
585 1 51.819886666 31OCT2019 04NOV19:06:04:17
618 3 19906.248261 31OCT2019 04NOV19:06:04:17
646 1 0.8131099221 31OCT2019 04NOV19:06:04:17
654 1 0.5990375342 31OCT2019 04NOV19:06:04:17
767 1 55.752171141 31OCT2019 04NOV19:06:04:17
1606 1 72.703588822 31OCT2019 04NOV19:06:04:17
4745 1 41.822827446 31OCT2019 04NOV19:06:04:17
4750 1 49.4281835 31OCT2019 04NOV19:06:04:17
4769 2 121.30741193 31OCT2019 04NOV19:06:04:17
4964 1 22.321415208 31OCT2019 04NOV19:06:04:17
5585 1 36.881990209 31OCT2019 04NOV19:06:04:17
5601 1 1.272046705 31OCT2019 04NOV19:06:04:17
5608 1 4.3474306069 31OCT2019 04NOV19:06:04:17
6993 1 9.0944832076 31OCT2019 04NOV19:06:04:17
7267 2 656.02905222 31OCT2019 04NOV19:06:04:17
7793 1 17.126170637 31OCT2019 04NOV19:06:04:17
8160 2 236.30757553 31OCT2019 04NOV19:06:04:17
8691 1 28.30618198 31OCT2019 04NOV19:06:04:17
8758 1 54.684903158 31OCT2019 04NOV19:06:04:17
113 1 1.383013089 30NOV2019 03DEC19:09:04:37
253 2 147.83478255 30NOV2019 03DEC19:09:04:37
254 2 27.863723732 30NOV2019 03DEC19:09:04:37
424 2 16.831105784 30NOV2019 03DEC19:09:04:37
467 1 47.380406535 30NOV2019 03DEC19:09:04:37
509 1 206.10572211 30NOV2019 03DEC19:09:04:37
585 1 51.517863612 30NOV2019 03DEC19:09:04:37
618 3 19919.488195 30NOV2019 03DEC19:09:04:37
646 1 0.5350457603 30NOV2019 03DEC19:09:04:37
654 1 0.8911858103 30NOV2019 03DEC19:09:04:37
767 2 1129.7818647 30NOV2019 03DEC19:09:04:37
1606 2 2321.6419581 30NOV2019 03DEC19:09:04:37
4745 1 43.140229652 30NOV2019 03DEC19:09:04:37
4750 1 50.863402634 30NOV2019 03DEC19:09:04:37
4769 2 5.315273714 30NOV2019 03DEC19:09:04:37
4964 2 67.044439404 30NOV2019 03DEC19:09:04:37
5585 1 36.527428583 30NOV2019 03DEC19:09:04:37
5601 1 0.9539986273 30NOV2019 03DEC19:09:04:37
5608 1 4.0439109935 30NOV2019 03DEC19:09:04:37
6993 1 8.9655147898 30NOV2019 03DEC19:09:04:37
7267 2 620.49757145 30NOV2019 03DEC19:09:04:37
7793 1 16.896373249 30NOV2019 03DEC19:09:04:37
8160 2 220.08648564 30NOV2019 03DEC19:09:04:37
8691 1 27.894940809 30NOV2019 03DEC19:09:04:37
8758 1 54.371032858 30NOV2019 03DEC19:09:04:37
;

proc sort data=have;
by id nemo_date;
run;

proc transpose data=have out=trans (drop=_name_);
by id;
var stage;
id month;
run;

data want;
set trans; /* get the wide dataset */
length
  month $9
  count revised overlay 8
;
if _n_ = 1
then do;
  declare hash lookup();
  lookup.definekey('count');
  lookup.definedata('revised','month');
  lookup.definedone();
end;
lookup.clear();
count = 0;
do until (last.id);
  set have;
  by id;
  count + 1;
  if not last.id
  then do;
    if month2 = . and stage = 2 then month2 = count;
    if month3 = . and stage = 3 then month3 = count;
    revised = ecl;
    month = put(nemo_date,monname.);
    rc = lookup.add();
  end;
end;
revised = .;
month = '';
/* we now have the last observation for a given ID in the PDV */
select (stage);
  when (1) if month2 ne . or month3 ne .
    then do;
      count = min(month2,month3);
      rc = lookup.find(); /* retrieves revised, month for earliest switch to 2 or 3 */
    end;
  when (2) if month3 ne .
    then do;
      rc = lookup.find(key:month3); /* retrieves revised, month for month3 */
    end;
  when (3) month = '';
end;
overlay = revised - ecl;
drop count month2 month3 rc stage ecl nemo_date run_date;
run;

proc print data=want noobs;
run;

Result (column names omitted because they use too much space):

113 2 2 2 2 1 1 1 1 1 1 1 1 December 20.47 19.08 
253 2 2 2 2 2 2 2 2 2 2 2 2   . . 
254 2 2 2 2 2 2 2 2 2 2 2 2   . . 
424 2 2 2 2 2 2 2 2 2 2 2 2   . . 
467 1 1 1 1 1 1 1 1 1 1 1 1   . . 
509 1 1 1 1 1 1 1 1 1 1 1 1   . . 
585 1 1 1 1 1 1 1 1 1 1 1 1   . . 
618 3 3 3 3 3 3 3 3 3 3 3 3   . . 
646 1 1 1 1 1 1 1 1 1 1 1 1   . . 
654 1 1 1 1 1 1 1 1 1 1 1 1   . . 
767 2 2 2 2 2 1 1 1 2 1 1 2   . . 
1606 2 2 1 1 1 1 1 1 1 1 1 2   . . 
4745 1 1 1 1 1 2 1 1 1 1 1 1 May 340.07 296.93 
4750 2 2 2 2 2 2 2 2 2 1 1 1 December 1446.59 1395.73 
4769 3 3 3 3 3 3 3 3 3 3 2 2 December 646.00 640.69 
4964 2 2 2 2 2 2 1 1 1 2 1 2   . . 
5585 1 1 2 2 1 1 1 1 1 1 1 1 February 1110.05 1073.52 
5601 2 2 2 2 2 2 1 1 1 1 1 1 December 23.42 22.47 
5608 1 2 2 2 1 1 1 1 1 1 1 1 January 35.91 31.87 
6993 1 2 2 2 1 1 1 1 1 1 1 1 January 129.07 120.11 
7267 2 2 2 2 3 3 3 3 3 2 2 2 April 932.43 311.93 
7793 1 1 1 2 2 2 1 1 1 1 1 1 March 923.28 906.39 
8160 2 2 2 2 2 2 2 2 2 2 2 2   . . 
8691 1 1 1 2 2 2 2 2 1 1 1 1 March 338.56 310.67 
8758 1 1 1 1 2 2 2 1 1 1 1 1 April 2110.61 2056.24

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

vnreddy · Posted 12-05-2019 08:01 AM

Hi @Kurt_Bremser

thank you so much, everything works fine..except a small data validation error

ID 4964 should be December but we are getting blank

Kurt_Bremser · Posted 12-05-2019 08:07 AM

@vnreddy wrote:

Hi @Kurt_Bremser

thank you so much, everything works fine..except a small data validation error

ID 4964 should be December but we are getting blank

Why?

4964 has stage 2 in the last month, and no stage 3 anywhere.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

vnreddy · Posted 12-05-2019 08:13 AM

last month is not in stage 3, eventually business intention is to capture the stage 2 and 3 from mon1 to mon11

if you see 4745 also has 2 it captured correctly,

Kurt_Bremser · Posted 12-05-2019 08:47 AM

4745 has a last stage of 1, so it is completely different from 4964 (last stage 2).

Please follow Maxim 42 and state your business rule in a clear and understandable manner. Like

if last stage is 3, leave results empty
if last stage is 2, search for earliest occurence of stage 3
if last stage is 1 .....

Quote from you:

*) MONTH business rule: Stage: If last month is 3 then Month should be NULL
then search stage 2 from last month in mon1 to mon11 for stage 3 if true month name else NULL
then search stage 1 from last month in mon1 to mon11 for stage 3,2 if ture month name else NULL

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

vnreddy · Posted 12-05-2019 11:07 AM

Hi @Kurt_Bremser

thank you so much, it's a data validation mistake from my end.

Business rules what we have used is absolutely right.

How to i remove the rows where last month is stage is missing e.g., ID 7079.

Thank you so much for everything,

vnreddy · Posted 12-06-2019 11:07 AM

Hi @Kurt_Bremser

how do i exclude the ID where last month (i.e.,November is . )

i need to delete the rows where last month has blank stage .

i could do it manually by giving the November month manually NE . but, going forward i don't want to change

the program every month.

Thank you.

vnreddy

Kurt_Bremser · Posted 12-06-2019 12:07 PM

When the do loop has finished, the select() block checks the stage value of the last observation for a single id. Just add a check for missing there that deletes (= doesn't write) the observation.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

SAS Innovate 2025: Register Now