topic Re: Arrays in New SAS User

Arrays

vnreddy — Mon, 02 Dec 2019 13:08:07 GMT




options symbolgen mlogic mprint;
data nemo_test;
set nemo_stg_lmth;
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;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      29:4    31:9    34:4    39:4    42:4    49:4    54:21   
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      36:9   44:9   51:9   
NOTE: Invalid character data, i=10.00 , at line 51 column 9.
NOTE: Invalid numeric data, Month='*' , at line 54 column 21.
ERROR: Array subscript out of range at line 54 column 17.
OriginalApplicationID=45913 December=1 January=1 February=1 March=1 April=1 May=1 June=1 July=1 August=1 September=2 October=1
Month=* i=10 S_Month=  _ERROR_=1 _N_=749
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 749 observations read from the data set WORK.NEMO_STG_LMTH.
WARNING: The data set WORK.NEMO_TEST may be incomplete.  When this step was stopped there were 748 observations and 15 variables.
WARNING: Data set WORK.NEMO_TEST was not replaced because this step was stopped.

Hi I have data something like this:

when i try the array for last 11 months of data, i am getting array subscript error: can someone please help me in resolving the issue

business logic:

1) if October Month(11) is 1 and Month(1-10) is 2 then that Month name should be printed

2) if October Month(11) is 2 and Month(1-10) is 3 then where ever month starts with 3 then that month name should be printed

e.g., from the error message i want month to be September.

Re: Arrays

ed_sas_member — Mon, 02 Dec 2019 13:20:04 GMT

Hi @vnreddy

Could you please share some sample data?

Thank you!

Re: Arrays

Kurt_Bremser — Mon, 02 Dec 2019 13:27:03 GMT

Why are you defining Month as character when you later want to use it as an index into an array?

Why are you comparing your array elements with numbers, when they are defined as character?

And why are you working so hard at making your code unreadable? Writing spaghetti garbage like this is guaranteed to cause problems.

Please post the log of the whole step so we can locate exactly which lines SAS is complaining about, the contents of macro variable &dt_nm, and dataset nemo_stg_lmth in usable form (data step with datalines).

Re: Arrays

Kurt_Bremser — Mon, 02 Dec 2019 13:33:03 GMT

Also please post the code you actually ran, as your code as posted can NEVER work, see this what I got after I formatted this spaghetti ****:

data nemo_test;
set nemo_stg_lmth;
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; /* null statement! */
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; /* this surplus end causes the step to fail at compilation! */
if Month NE "" then
S_Month = vname(mth(Month));
run;

Re: Arrays

vnreddy — Mon, 02 Dec 2019 14:27:12 GMT

hi @Kurt_Bremser

sorry about that, i am quite new to SAS and learning to put things together by gathering

some useful tips from SAS community

complete code so far i have used:

and please see the sample data image:

%let todaysDate = %sysfunc(today(), date9.);

data _null_;
/*last_month = intnx("Month", "&todaysDate."d, -1, "END");*/
/*call symputx ('last_month', put(last_month, date9.));*/
Month_back2 = intnx("Month", "&todaysDate."d, -2, "END");
call symputx ('Month_back2', put(Month_back2, date9.));
Month_back3 = intnx("Month", "&todaysDate."d, -3, "END");
call symputx ('Month_back3', put(Month_back3, date9.));
Month_back4 = intnx("Month", "&todaysDate."d, -4, "END");
Month_back12 = intnx("Month", "&todaysDate."d, -12, "END");
call symputx ('Month_back12', put(Month_back12, date9.));
run;


%put where date between "&Month_back12"d and "&last_month"d;
%put &todaysDate &last_month &Month_back2 &Month_back3 &Month_back4 &Month_back12;


data nemo.nemo_ecl_prod_t;
set nemo.nemo_ecl_prod;
run;

/*
proc contents data=nemo.nemo_ecl_prod_t;
run;
*/

data work.nemo_twelve_month (keep=OriginalApplicationID Stage ECL run_date nemo_date);
set nemo.nemo_ecl_prod_t;
where nemo_date between "&Month_back12."d and "&Month_back2"d /*"&last_month"d*/ /*and latest_run = 'Y'*/;
run;

PROC SQL;
CREATE TABLE WORK.nemo_month_fmt AS 
SELECT OriginalApplicationID, 
       Stage, 
       ECL, 
	   run_date max(run_date) as m_rundate,
       nemo_date,
	   nemo_date FORMAT=MONNAME9. AS Month
      FROM WORK.NEMO_TWELVE_MONTH;
QUIT;

data nemo_month_fmt_;
set nemo_month_fmt;
where OriginalApplicationID NE . ;
run;
/*
proc sort data=nemo_month_fmt_;
by originalapplicationid;
run;
*/

proc sql;
create table nemo_dup_month as
select *
from nemo_month_fmt
group by nemo_date
/*having nemo_date = max(nemo_date);*/
having m_rundate = max(m_rundate);
quit;

proc sort data=nemo_dup_month;
by originalapplicationid;
run;

proc transpose data=nemo_dup_month out=nemo_m_fmt(drop=_name_);
by originalapplicationid;
var stage;
id Month;
run;


options symbolgen mlogic mprint;
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;

Re: Arrays

vnreddy — Mon, 02 Dec 2019 14:59:04 GMT

OriginalApplicationID Stage ECL Nemo_date
7267 2 310.4922578 31-Dec-18
7267 2 284.1795028 31-Jan-19
7267 2 305.9252432 28-Feb-19
7267 2 647.556714 31-Mar-19
7267 3 932.4284102 30-Apr-19
7267 3 839.5357268 31-May-19
7267 3 806.460993 30-Jun-19
7267 3 739.9468882 31-Jul-19
7267 3 718.860982 31-Aug-19
7267 2 635.573145 30-Sep-19
7267 2 656.0290522 31-Oct-19
7793 1 35.58316204 31-Dec-18
7793 1 35.96957208 31-Jan-19
7793 1 55.3785582 28-Feb-19
7793 2 923.2837645 31-Mar-19
7793 2 919.0402749 30-Apr-19
7793 2 930.659084 31-May-19
7793 1 34.8811567 30-Jun-19
7793 1 34.65632422 31-Jul-19
7793 1 33.70968438 31-Aug-19
7793 1 33.07500636 30-Sep-19
7793 1 17.12617064 31-Oct-19
9434 1 7.189149224 31-Dec-18
9434 1 7.14900696 31-Jan-19
9434 1 7.022060722 28-Feb-19
9434 1 6.916268217 31-Mar-19
9434 1 6.792931518 30-Apr-19
9434 1 6.76085288 31-May-19
9434 1 6.681789709 30-Jun-19
9434 1 10.17436687 31-Jul-19
9434 2 891.1101093 31-Aug-19
9434 2 753.416957 30-Sep-19
9434 1 35.51067285 31-Oct-19
9523 1 20.49622488 31-Dec-18
9523 1 20.62410657 31-Jan-19
9523 1 20.31687893 28-Feb-19
9523 1 20.08796033 31-Mar-19
9523 1 19.77000246 30-Apr-19
9523 1 19.62216115 31-May-19
9523 1 19.48598417 30-Jun-19
9523 1 19.21695299 31-Jul-19
9523 1 18.77420846 31-Aug-19
9523 1 18.46826511 30-Sep-19
9523 1 18.18600003 31-Oct-19
9532 2 1622.924956 31-Dec-18
9532 2 1599.94398 31-Jan-19
9532 2 1550.340788 28-Feb-19
9532 2 1544.018303 31-Mar-19
9532 2 1574.572185 30-Apr-19
9532 2 1524.781151 31-May-19
9532 1 225.2265888 30-Jun-19
9532 1 225.396246 31-Jul-19
9532 1 194.0203314 31-Aug-19
9532 1 188.694135 30-Sep-19
9532 1 186.1166642 31-Oct-19

Re: Arrays

Kurt_Bremser — Mon, 02 Dec 2019 15:15:21 GMT

There is no variable run_date in your data.

Re: Arrays

vnreddy — Mon, 02 Dec 2019 15:23:03 GMT

hi @Kurt_Bremser

sorry i have excluded run_date from that piece of data, i have multiple run_dates.

anyway we don't that column in output report, i have taken that column just extract latest run data.

Re: Arrays

Kurt_Bremser — Mon, 02 Dec 2019 15:24:54 GMT

Your code uses run_date, so we need that in the example dataset.

Re: Arrays

vnreddy — Mon, 02 Dec 2019 15:41:26 GMT

OriginalApplicationID Stage ECL Nemo_date run_date
7267 2 310.4922578 31-Dec-18 03JAN19:06:10:18
7267 2 284.1795028 31-Jan-19 04FEB19:06:04:44
7267 2 305.9252432 28-Feb-19 04MAR19:10:55:37
7267 2 647.556714 31-Mar-19 02APR19:09:55:12
7267 3 932.4284102 30-Apr-19 02MAY19:06:46:31
7267 3 839.5357268 31-May-19 04JUN19:06:05:27
7267 3 806.460993 30-Jun-19 02JUL19:06:05:38
7267 3 739.9468882 31-Jul-19 02AUG19:16:50:19
7267 3 718.860982 31-Aug-19 03SEP19:06:03:59
7267 2 635.573145 30-Sep-19 02OCT19:06:04:18
7267 2 656.0290522 31-Oct-19 04NOV19:06:04:17
7793 1 35.58316204 31-Dec-18 03JAN19:06:10:18
7793 1 35.96957208 31-Jan-19 04FEB19:06:04:44
7793 1 55.3785582 28-Feb-19 04MAR19:10:55:37
7793 2 923.2837645 31-Mar-19 02APR19:09:55:12
7793 2 919.0402749 30-Apr-19 02MAY19:06:46:31
7793 2 930.659084 31-May-19 04JUN19:06:05:27
7793 1 34.8811567 30-Jun-19 02JUL19:06:05:38
7793 1 34.65632422 31-Jul-19 02AUG19:16:50:19
7793 1 33.70968438 31-Aug-19 03SEP19:06:03:59
7793 1 33.07500636 30-Sep-19 02OCT19:06:04:18
7793 1 17.12617064 31-Oct-19 04NOV19:06:04:17
9434 1 7.189149224 31-Dec-18 03JAN19:06:10:18
9434 1 7.14900696 31-Jan-19 04FEB19:06:04:44
9434 1 7.022060722 28-Feb-19 04MAR19:10:55:37
9434 1 6.916268217 31-Mar-19 02APR19:09:55:12
9434 1 6.792931518 30-Apr-19 02MAY19:06:46:31
9434 1 6.76085288 31-May-19 04JUN19:06:05:27
9434 1 6.681789709 30-Jun-19 02JUL19:06:05:38
9434 1 10.17436687 31-Jul-19 02AUG19:16:50:19
9434 2 891.1101093 31-Aug-19 03SEP19:06:03:59
9434 2 753.416957 30-Sep-19 02OCT19:06:04:18
9434 1 35.51067285 31-Oct-19 04NOV19:06:04:17
9523 1 20.49622488 31-Dec-18 03JAN19:06:10:18
9523 1 20.62410657 31-Jan-19 04FEB19:06:04:44
9523 1 20.31687893 28-Feb-19 04MAR19:10:55:37
9523 1 20.08796033 31-Mar-19 02APR19:09:55:12
9523 1 19.77000246 30-Apr-19 02MAY19:06:46:31
9523 1 19.62216115 31-May-19 04JUN19:06:05:27
9523 1 19.48598417 30-Jun-19 02JUL19:06:05:38
9523 1 19.21695299 31-Jul-19 02AUG19:16:50:19
9523 1 18.77420846 31-Aug-19 03SEP19:06:03:59
9523 1 18.46826511 30-Sep-19 02OCT19:06:04:18
9523 1 18.18600003 31-Oct-19 04NOV19:06:04:17
9532 2 1622.924956 31-Dec-18 03JAN19:06:10:18
9532 2 1599.94398 31-Jan-19 04FEB19:06:04:44
9532 2 1550.340788 28-Feb-19 04MAR19:10:55:37
9532 2 1544.018303 31-Mar-19 02APR19:09:55:12
9532 2 1574.572185 30-Apr-19 02MAY19:06:46:31
9532 2 1524.781151 31-May-19 04JUN19:06:05:27
9532 1 225.2265888 30-Jun-19 02JUL19:06:05:38
9532 1 225.396246 31-Jul-19 02AUG19:16:50:19
9532 1 194.0203314 31-Aug-19 03SEP19:06:03:59
9532 1 188.694135 30-Sep-19 02OCT19:06:04:18
9532 1 186.1166642 31-Oct-19 04NOV19:06:04:17

Re: Arrays

Kurt_Bremser — Mon, 02 Dec 2019 16:50:22 GMT

This code runs without ERRORS, WARNINGS or any extraneous NOTEs:

data nemo_ecl_prod;
input OriginalApplicationID Stage ECL Nemo_date :date9. run_date :date9.;
format Nemo_date run_date yymmddd10.;
datalines;
7267 2 310.4922578 31-Dec-18 03JAN19:06:10:18
7267 2 284.1795028 31-Jan-19 04FEB19:06:04:44
7267 2 305.9252432 28-Feb-19 04MAR19:10:55:37
7267 2 647.556714 31-Mar-19 02APR19:09:55:12
7267 3 932.4284102 30-Apr-19 02MAY19:06:46:31
7267 3 839.5357268 31-May-19 04JUN19:06:05:27
7267 3 806.460993 30-Jun-19 02JUL19:06:05:38
7267 3 739.9468882 31-Jul-19 02AUG19:16:50:19
7267 3 718.860982 31-Aug-19 03SEP19:06:03:59
7267 2 635.573145 30-Sep-19 02OCT19:06:04:18
7267 2 656.0290522 31-Oct-19 04NOV19:06:04:17
7793 1 35.58316204 31-Dec-18 03JAN19:06:10:18
7793 1 35.96957208 31-Jan-19 04FEB19:06:04:44
7793 1 55.3785582 28-Feb-19 04MAR19:10:55:37
7793 2 923.2837645 31-Mar-19 02APR19:09:55:12
7793 2 919.0402749 30-Apr-19 02MAY19:06:46:31
7793 2 930.659084 31-May-19 04JUN19:06:05:27
7793 1 34.8811567 30-Jun-19 02JUL19:06:05:38
7793 1 34.65632422 31-Jul-19 02AUG19:16:50:19
7793 1 33.70968438 31-Aug-19 03SEP19:06:03:59
7793 1 33.07500636 30-Sep-19 02OCT19:06:04:18
7793 1 17.12617064 31-Oct-19 04NOV19:06:04:17
9434 1 7.189149224 31-Dec-18 03JAN19:06:10:18
9434 1 7.14900696 31-Jan-19 04FEB19:06:04:44
9434 1 7.022060722 28-Feb-19 04MAR19:10:55:37
9434 1 6.916268217 31-Mar-19 02APR19:09:55:12
9434 1 6.792931518 30-Apr-19 02MAY19:06:46:31
9434 1 6.76085288 31-May-19 04JUN19:06:05:27
9434 1 6.681789709 30-Jun-19 02JUL19:06:05:38
9434 1 10.17436687 31-Jul-19 02AUG19:16:50:19
9434 2 891.1101093 31-Aug-19 03SEP19:06:03:59
9434 2 753.416957 30-Sep-19 02OCT19:06:04:18
9434 1 35.51067285 31-Oct-19 04NOV19:06:04:17
9523 1 20.49622488 31-Dec-18 03JAN19:06:10:18
9523 1 20.62410657 31-Jan-19 04FEB19:06:04:44
9523 1 20.31687893 28-Feb-19 04MAR19:10:55:37
9523 1 20.08796033 31-Mar-19 02APR19:09:55:12
9523 1 19.77000246 30-Apr-19 02MAY19:06:46:31
9523 1 19.62216115 31-May-19 04JUN19:06:05:27
9523 1 19.48598417 30-Jun-19 02JUL19:06:05:38
9523 1 19.21695299 31-Jul-19 02AUG19:16:50:19
9523 1 18.77420846 31-Aug-19 03SEP19:06:03:59
9523 1 18.46826511 30-Sep-19 02OCT19:06:04:18
9523 1 18.18600003 31-Oct-19 04NOV19:06:04:17
9532 2 1622.924956 31-Dec-18 03JAN19:06:10:18
9532 2 1599.94398 31-Jan-19 04FEB19:06:04:44
9532 2 1550.340788 28-Feb-19 04MAR19:10:55:37
9532 2 1544.018303 31-Mar-19 02APR19:09:55:12
9532 2 1574.572185 30-Apr-19 02MAY19:06:46:31
9532 2 1524.781151 31-May-19 04JUN19:06:05:27
9532 1 225.2265888 30-Jun-19 02JUL19:06:05:38
9532 1 225.396246 31-Jul-19 02AUG19:16:50:19
9532 1 194.0203314 31-Aug-19 03SEP19:06:03:59
9532 1 188.694135 30-Sep-19 02OCT19:06:04:18
9532 1 186.1166642 31-Oct-19 04NOV19:06:04:17
;

%let todaysDate = %sysfunc(today(), date9.);

data _null_;
last_month = intnx("Month", "&todaysDate."d, -1, "END");
call symputx ('last_month', put(last_month, date9.));
Month_back2 = intnx("Month", "&todaysDate."d, -2, "END");
call symputx ('Month_back2', put(Month_back2, date9.));
Month_back3 = intnx("Month", "&todaysDate."d, -3, "END");
call symputx ('Month_back3', put(Month_back3, date9.));
Month_back4 = intnx("Month", "&todaysDate."d, -4, "END");
Month_back12 = intnx("Month", "&todaysDate."d, -12, "END");
call symputx ('Month_back12', put(Month_back12, date9.));
run;


%put where date between "&Month_back12"d and "&last_month"d;
%put &todaysDate &last_month &Month_back2 &Month_back3 &Month_back12;


data nemo_ecl_prod_t;
set nemo_ecl_prod;
run;

/*
proc contents data=nemo.nemo_ecl_prod_t;
run;
*/

data nemo_twelve_month (keep=OriginalApplicationID Stage ECL run_date nemo_date);
set nemo_ecl_prod_t;
where nemo_date between "&Month_back12."d and "&Month_back2"d /*"&last_month"d*/ /*and latest_run = 'Y'*/;
run;

PROC SQL;
CREATE TABLE nemo_month_fmt AS 
SELECT OriginalApplicationID, 
       Stage, 
       ECL, 
	   run_date max(run_date) as m_rundate,
       nemo_date,
	   nemo_date FORMAT=MONNAME9. AS Month
      FROM NEMO_TWELVE_MONTH;
QUIT;

data nemo_month_fmt_;
set nemo_month_fmt;
where OriginalApplicationID NE . ;
run;
/*
proc sort data=nemo_month_fmt_;
by originalapplicationid;
run;
*/

proc sql;
create table nemo_dup_month as
select *
from nemo_month_fmt
group by nemo_date
/*having nemo_date = max(nemo_date);*/
having m_rundate = max(m_rundate);
quit;

proc sort data=nemo_dup_month;
by originalapplicationid;
run;

proc transpose data=nemo_dup_month out=nemo_m_fmt(drop=_name_);
by originalapplicationid;
var stage;
id Month;
run;


options symbolgen mlogic mprint;
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;

Re: Arrays

vnreddy — Mon, 02 Dec 2019 17:06:53 GMT

hi @Kurt_Bremser

program is running fine without any errors, unfortunately, it's not fulfilling my requirement.

S_Month should be something like below:

OriginalApplicationID S_Month

7267 April
7793 March
9434 August
9523
9532 December

Re: Arrays

Kurt_Bremser — Mon, 02 Dec 2019 17:22:29 GMT

What is the logical rule for selecting the month?

Re: Arrays

vnreddy — Mon, 02 Dec 2019 17:25:20 GMT

business logic:

1) if October Month(11) is 1 and Month(1-10) is 2 then that Month name should be printed

2) if October Month(11) is 2 and Month(1-10) is 3 then where ever month starts with 3 then that month name should be printed

Re: Arrays

Kurt_Bremser — Mon, 02 Dec 2019 18:09:22 GMT

Then that whole befuddling operation is unnecessary, one single data step does it:

data nemo_ecl_prod;
input OriginalApplicationID Stage ECL Nemo_date :date9. run_date :date9.;
format Nemo_date run_date yymmddd10.;
datalines;
7267 2 310.4922578 31-Dec-18 03JAN19:06:10:18
7267 2 284.1795028 31-Jan-19 04FEB19:06:04:44
7267 2 305.9252432 28-Feb-19 04MAR19:10:55:37
7267 2 647.556714 31-Mar-19 02APR19:09:55:12
7267 3 932.4284102 30-Apr-19 02MAY19:06:46:31
7267 3 839.5357268 31-May-19 04JUN19:06:05:27
7267 3 806.460993 30-Jun-19 02JUL19:06:05:38
7267 3 739.9468882 31-Jul-19 02AUG19:16:50:19
7267 3 718.860982 31-Aug-19 03SEP19:06:03:59
7267 2 635.573145 30-Sep-19 02OCT19:06:04:18
7267 2 656.0290522 31-Oct-19 04NOV19:06:04:17
7793 1 35.58316204 31-Dec-18 03JAN19:06:10:18
7793 1 35.96957208 31-Jan-19 04FEB19:06:04:44
7793 1 55.3785582 28-Feb-19 04MAR19:10:55:37
7793 2 923.2837645 31-Mar-19 02APR19:09:55:12
7793 2 919.0402749 30-Apr-19 02MAY19:06:46:31
7793 2 930.659084 31-May-19 04JUN19:06:05:27
7793 1 34.8811567 30-Jun-19 02JUL19:06:05:38
7793 1 34.65632422 31-Jul-19 02AUG19:16:50:19
7793 1 33.70968438 31-Aug-19 03SEP19:06:03:59
7793 1 33.07500636 30-Sep-19 02OCT19:06:04:18
7793 1 17.12617064 31-Oct-19 04NOV19:06:04:17
9434 1 7.189149224 31-Dec-18 03JAN19:06:10:18
9434 1 7.14900696 31-Jan-19 04FEB19:06:04:44
9434 1 7.022060722 28-Feb-19 04MAR19:10:55:37
9434 1 6.916268217 31-Mar-19 02APR19:09:55:12
9434 1 6.792931518 30-Apr-19 02MAY19:06:46:31
9434 1 6.76085288 31-May-19 04JUN19:06:05:27
9434 1 6.681789709 30-Jun-19 02JUL19:06:05:38
9434 1 10.17436687 31-Jul-19 02AUG19:16:50:19
9434 2 891.1101093 31-Aug-19 03SEP19:06:03:59
9434 2 753.416957 30-Sep-19 02OCT19:06:04:18
9434 1 35.51067285 31-Oct-19 04NOV19:06:04:17
9523 1 20.49622488 31-Dec-18 03JAN19:06:10:18
9523 1 20.62410657 31-Jan-19 04FEB19:06:04:44
9523 1 20.31687893 28-Feb-19 04MAR19:10:55:37
9523 1 20.08796033 31-Mar-19 02APR19:09:55:12
9523 1 19.77000246 30-Apr-19 02MAY19:06:46:31
9523 1 19.62216115 31-May-19 04JUN19:06:05:27
9523 1 19.48598417 30-Jun-19 02JUL19:06:05:38
9523 1 19.21695299 31-Jul-19 02AUG19:16:50:19
9523 1 18.77420846 31-Aug-19 03SEP19:06:03:59
9523 1 18.46826511 30-Sep-19 02OCT19:06:04:18
9523 1 18.18600003 31-Oct-19 04NOV19:06:04:17
9532 2 1622.924956 31-Dec-18 03JAN19:06:10:18
9532 2 1599.94398 31-Jan-19 04FEB19:06:04:44
9532 2 1550.340788 28-Feb-19 04MAR19:10:55:37
9532 2 1544.018303 31-Mar-19 02APR19:09:55:12
9532 2 1574.572185 30-Apr-19 02MAY19:06:46:31
9532 2 1524.781151 31-May-19 04JUN19:06:05:27
9532 1 225.2265888 30-Jun-19 02JUL19:06:05:38
9532 1 225.396246 31-Jul-19 02AUG19:16:50:19
9532 1 194.0203314 31-Aug-19 03SEP19:06:03:59
9532 1 188.694135 30-Sep-19 02OCT19:06:04:18
9532 1 186.1166642 31-Oct-19 04NOV19:06:04:17
;

data want;
set nemo_ecl_prod;
by OriginalApplicationID;
retain
  month2
  month3
;
if first.OriginalApplicationID
then do;
  month2 = .;
  month3 = .;
  month = .;
end;
if not last.OriginalApplicationID
then do;
  if month2 = . and stage = 2 then month2 = month(nemo_date);
  if month3 = . and stage = 3 then month3 = month(nemo_date);
end;
if last.OriginalApplicationID;
if stage = 1 and month2 ne . then month = put(mdy(month2,1,2019),monname.);
if stage = 2 and month3 ne . then month = put(mdy(month3,1,2019),monname.);
keep OriginalApplicationID month;
run;

proc print data=want noobs;
run;

Result:

OriginalApplicationID	month
7267	April
7793	March
9434	August
9523	 
9532	December

Re: Arrays

vnreddy — Tue, 03 Dec 2019 11:18:13 GMT

hi @Kurt_Bremser

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.

Re: Arrays

Kurt_Bremser — Tue, 03 Dec 2019 11:38:46 GMT

Remove the line

  month = .;

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

Re: Arrays

vnreddy — Tue, 03 Dec 2019 11:48:29 GMT

Thank you so much

Re: Arrays

vnreddy — Tue, 03 Dec 2019 15:48:39 GMT

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

Re: Arrays

ballardw — Wed, 04 Dec 2019 23:40:00 GMT

@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.