Arrays

[vnreddy]

Hi @Kurt_Bremser 

i tried something like this :

23         GOPTIONS ACCESSIBLE;
24         %let Monthn12=%sysfunc(intnx(month,%sysfunc(today()),-1),MONNAME.);
25         %put &Monthn12;
November
26         
27         options validvarname=any;
28         data test;
29         set nemo_rev_mth;
30         /*where ' November'n NE .;*/
31         where  &Monthn12 NE .;
ERROR: Variable November is not on file WORK.NEMO_REV_MTH.
32         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0 observations and 24 variables.
WARNING: Data set WORK.TEST was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              627.40k
      OS Memory           22108.00k
      Timestamp           09/12/2019 12:57:18 PM

 working when i am using something like this where ' November'n NE .;

but when i use the Macro for November month it's giving me Variable November is not available.

As we know November Month is available in out dataset in numeric format

[Kurt_Bremser]

My code does NOT have month variables, it has month values, and you should do the filtering there, as described in my previous post. After the transposition, the column names can have all kinds of "funny" effects, like leading blanks, which seems to happen in your case.

[vnreddy]

hi @Kurt_Bremser 

in this case how can i do after your step dynamically,

as you know going forward i cannot change month names manually.

[Kurt_Bremser]

Do all necessary operations in the long format, keep the transpose as the last step, after which you either do print or export, but nothing else.

[vnreddy]

hi @Kurt_Bremser 

sorry for bothering, i just traced out one issue from the results after using your program

below id should give March Month in Month Column but we are getting December.

1584 2 2 2 3 1 1 1 1 1 1 1 1 December

[vnreddy]

complete error list:

Business rule:

when a customer make 3 months of outstanding due then he will fall back to stage 1, still we need to capture stage 2 or stage 3 month Name
we missed to capture such scenarios from stage 3 to stage 1

ID December January February March April May June July August September October NovemberError_Month Correct_Month
3699400 2 2 2 3 3 3 3 3 3 2 2 1 December March
8816700 2 2 2 2 2 3 2 2 2 2 2 1 December May
1385840 2 2 2 3 1 1 1 1 1 1 1 1 December March
2473900 2 2 2 2 2 2 3 3 3 3 1 1 December June
2723940 2 3 3 3 2 2 1 1 1 1 1 1 December January
6513440 2 3 3 3 3 3 3 3 3 2 2 1 December January
6998070 1 1 1 1 1 1 2 2 3 2 2 1 June August
7778480 2 3 1 1 1 1 1 1 1 1 1 1 December January

data :

ID Stage ECL Nemo_date run_date
3699400 3 1862.9879699 30JUN2019 02JUL19:06:05:38
3699400 1 24.129513866 30NOV2019 03DEC19:09:04:37
3699400 3 1822.5610297 31MAR2019 02APR19:09:55:12
3699400 3 1972.1525926 31AUG2019 03SEP19:06:03:59
3699400 2 1617.4190637 31DEC2018 03JAN19:06:10:18
3699400 2 1378.1121121 31OCT2019 04NOV19:06:04:17
3699400 2 1121.5341175 30SEP2019 02OCT19:06:04:18
3699400 3 1805.0156904 31MAY2019 04JUN19:06:05:27
3699400 3 1866.5382377 30APR2019 02MAY19:06:46:31
3699400 2 1748.5576675 28FEB2019 04MAR19:10:55:37
3699400 2 1095.2350804 31JAN2019 04FEB19:06:04:44
3699400 3 1908.4267168 31JUL2019 02AUG19:16:50:19
8816700 2 1638.3432736 31OCT2019 04NOV19:06:04:17
8816700 2 1635.6142192 30SEP2019 02OCT19:06:04:18
8816700 3 1987.4549959 31MAY2019 04JUN19:06:05:27
8816700 2 1974.8782796 31AUG2019 03SEP19:06:03:59
8816700 2 1946.6432949 30APR2019 02MAY19:06:46:31
8816700 2 2188.6786306 31MAR2019 02APR19:09:55:12
8816700 2 2111.3128619 28FEB2019 04MAR19:10:55:37
8816700 2 2118.6497143 30JUN2019 02JUL19:06:05:38
8816700 1 41.213089542 30NOV2019 03DEC19:09:04:37
8816700 2 1753.7316624 31DEC2018 03JAN19:06:10:18
8816700 2 2085.2962381 31JUL2019 02AUG19:16:50:19
8816700 2 1847.9767636 31JAN2019 04FEB19:06:04:44
1372250 2 2392.9752707 30SEP2019 02OCT19:06:04:18
1372250 2 2058.4388742 31MAY2019 04JUN19:06:05:27
1372250 2 2689.4439506 30JUN2019 02JUL19:06:05:38
1372250 1 61.928508158 30NOV2019 03DEC19:09:04:37
1372250 2 2320.2470165 28FEB2019 04MAR19:10:55:37
1372250 2 2561.9891722 31JAN2019 04FEB19:06:04:44
1372250 2 2179.8143277 31OCT2019 04NOV19:06:04:17
1372250 2 2091.5381932 30APR2019 02MAY19:06:46:31
1372250 2 2233.2878468 31MAR2019 02APR19:09:55:12
1372250 2 3324.9728096 31AUG2019 03SEP19:06:03:59
1372250 2 2428.658178 31JUL2019 02AUG19:16:50:19
1372250 3 2647.8705954 31DEC2018 03JAN19:06:10:18
1385840 3 1528.3795764 31MAR2019 02APR19:09:55:12
1385840 1 33.304034973 31MAY2019 04JUN19:06:05:27
1385840 1 13.871871515 30JUN2019 02JUL19:06:05:38
1385840 2 310.29128277 31JAN2019 04FEB19:06:04:44
1385840 2 1275.3625672 28FEB2019 04MAR19:10:55:37
1385840 1 33.903650152 30APR2019 02MAY19:06:46:31
1385840 1 7.3714552937 31AUG2019 03SEP19:06:03:59
1385840 2 693.48616717 31DEC2018 03JAN19:06:10:18
1385840 1 6.9487856666 31OCT2019 04NOV19:06:04:17
1385840 1 6.8407567495 30NOV2019 03DEC19:09:04:37
1385840 1 7.5434187432 31JUL2019 02AUG19:16:50:19
1385840 1 6.7427855599 30SEP2019 02OCT19:06:04:18
2473900 2 222.76496269 31JAN2019 04FEB19:06:04:44
2473900 3 264.03817664 30JUN2019 02JUL19:06:05:38
2473900 2 210.12901395 31MAY2019 04JUN19:06:05:27
2473900 2 206.11605391 28FEB2019 04MAR19:10:55:37
2473900 1 8.2226265119 31OCT2019 04NOV19:06:04:17
2473900 3 254.37658964 30SEP2019 02OCT19:06:04:18
2473900 1 8.1958360419 30NOV2019 03DEC19:09:04:37
2473900 2 256.69015671 31DEC2018 03JAN19:06:10:18
2473900 2 242.44196173 30APR2019 02MAY19:06:46:31
2473900 3 268.45912006 31AUG2019 03SEP19:06:03:59
2473900 3 268.48143296 31JUL2019 02AUG19:16:50:19
2473900 2 233.32308168 31MAR2019 02APR19:09:55:12
2723940 3 7263.3773218 31JAN2019 04FEB19:06:04:44
2723940 1 379.83785768 30JUN2019 02JUL19:06:05:38
2723940 2 5586.2952417 31MAY2019 04JUN19:06:05:27
2723940 3 7104.6913582 28FEB2019 04MAR19:10:55:37
2723940 2 7785.6022725 31DEC2018 03JAN19:06:10:18
2723940 1 170.53867738 31OCT2019 04NOV19:06:04:17
2723940 1 220.14435729 30NOV2019 03DEC19:09:04:37
2723940 1 147.0573559 30SEP2019 02OCT19:06:04:18
2723940 2 4393.4276698 30APR2019 02MAY19:06:46:31
2723940 1 221.05083336 31AUG2019 03SEP19:06:03:59
2723940 1 217.82040237 31JUL2019 02AUG19:16:50:19
2723940 3 7815.1398112 31MAR2019 02APR19:09:55:12
6513440 2 319.90358269 31DEC2018 03JAN19:06:10:18
6513440 3 319.26679957 30JUN2019 02JUL19:06:05:38
6513440 3 328.02321885 31MAY2019 04JUN19:06:05:27
6513440 3 328.35994406 28FEB2019 04MAR19:10:55:37
6513440 2 23.823343629 31OCT2019 04NOV19:06:04:17
6513440 1 4.7580492463 30NOV2019 03DEC19:09:04:37
6513440 2 32.211037076 30SEP2019 02OCT19:06:04:18
6513440 3 331.78648164 30APR2019 02MAY19:06:46:31
6513440 3 323.09935755 31AUG2019 03SEP19:06:03:59
6513440 3 330.56146708 31JUL2019 02AUG19:16:50:19
6513440 3 330.65791555 31MAR2019 02APR19:09:55:12
6513440 3 326.15410819 31JAN2019 04FEB19:06:04:44
6998070 2 147.62658372 30JUN2019 02JUL19:06:05:38
6998070 1 5.6677217381 31MAY2019 04JUN19:06:05:27
6998070 1 11.531858765 30NOV2019 03DEC19:09:04:37
6998070 2 366.43082272 31OCT2019 04NOV19:06:04:17
6998070 1 3.7603266544 30APR2019 02MAY19:06:46:31
6998070 2 375.13558347 30SEP2019 02OCT19:06:04:18
6998070 3 905.51831349 31AUG2019 03SEP19:06:03:59
6998070 1 5.72533218 28FEB2019 04MAR19:10:55:37
6998070 1 5.7364376314 31DEC2018 03JAN19:06:10:18
6998070 1 5.7328546261 31JAN2019 04FEB19:06:04:44
6998070 1 8.6072465204 31MAR2019 02APR19:09:55:12
6998070 2 843.49509942 31JUL2019 02AUG19:16:50:19
7778480 1 15.736316063 30JUN2019 02JUL19:06:05:38
7778480 1 16.037948667 28FEB2019 04MAR19:10:55:37
7778480 1 4.8521210794 31MAY2019 04JUN19:06:05:27
7778480 1 8.2745397182 30NOV2019 03DEC19:09:04:37
7778480 1 12.664463274 31OCT2019 04NOV19:06:04:17
7778480 1 4.9715427562 30APR2019 02MAY19:06:46:31
7778480 1 5.8862327226 30SEP2019 02OCT19:06:04:18
7778480 1 15.567752577 31AUG2019 03SEP19:06:03:59
7778480 2 537.87465031 31DEC2018 03JAN19:06:10:18
7778480 3 711.39039902 31JAN2019 04FEB19:06:04:44
7778480 1 15.737619037 31MAR2019 02APR19:09:55:12
7778480 1 10.61034303 31JUL2019 02AUG19:16:50:19

[vnreddy]

Hi @Kurt_Bremser 

i fixed it with a small addition in code:

by ID; count + 1 or 2;
  if not last.id
  then do;
	
  	if month1 = . and stage = 2 then month1 = count;
	if month1 = . and stage = 3 then month1 = count;

	if month2 = . and stage = 3 then month2 = count;
    if month2 = . and stage = 2 then month2 = count;

/*    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 = max(month1,month2,month3);

[vnreddy]

in some cases i come across with blank stage as well.

some of the ID starts from May or June , that mean i need to overcome the blanks as well