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.
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
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
There is no variable run_date in your data.
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.
Your code uses run_date, so we need that in the example dataset.
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
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;
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
What is the logical rule for selecting the month?
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).
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;
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;
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.