🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
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.
1 ACCEPTED SOLUTION
Accepted Solutions
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;
- DCSUG Virtual Meeting with Kirk Paul Lafler | 26-Jun-2024
- Wie kann ich SAS Visual Analytics effizient einsetzen, um die Ergebnisse meiner analytischen Modelle | 27-Jun-2024
- Ask the Expert: How Can I Use SAS® Optimization From Python? | 09-Jul-2024
- WUSS Virtual: Mastering Oncology Studies: A Comprehensive Guide for Programmers & Biostatisticians | 12-Jul-2024
- Ask the Expert: Jupyter Notebook: Your Coding Canvas | 16-Jul-2024
- MinnSUG Annual SAS Conference | 17-Jul-2024
- RTSUG presents a virtual webinar: Unlock Your Data with Python and SAS Viya | 18-Jul-2024
Mastering the WHERE Clause in PROC SQL
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.
-
1416 views
-
0 likes
-
-
