32 data getmonth;
33 set have;
34 by id;
35 retain change month;
36 length month $9;
ERROR: Character length cannot be used with numeric variable Month.
37 l_stage = lag(stage);
38 if first.id
39 then do;
40 change = 0;
41 month = '';
42 end;
43 else if l_stage ne stage then change = 1;
44 if stage in (2,3) and not last.id and (l_stage not in (2,3) or first.id) then month = idvar;
45 if last.id;
46 if change = 0 then month = '';
47 keep id month;
48 run;
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
41:11 46:28
WARNING: The variable id in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.GETMONTH may be incomplete. When this step was stopped there were 0 observations and 1 variables.
WARNING: Data set WORK.GETMONTH was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 604.40k
OS Memory 22364.00k
Timestamp 20/11/2019 03:20:42 PM
Step Count 178 Switch Count 0
... View more
i am sorry no luck
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 29:11 NOTE: Invalid numeric data, 'May' , at line 29 column 11. id=xxxxx May=1 June=1 July=1 August=1 September=1 October=1 month=. _ERROR_=1 _N_=1
... View more
i could see from the source data...this particular ID started from 31OCT2019 itself.
could you please help how to overcome such scenarios
... View more
last piece of code i got below error.
ERROR: Array subscript out of range at line 27 column 17. id=xxxxx May=. June=. July=. August=. September=. October=1 month= _ERROR_=1 _N_=1380
... View more
it contains data from May - Oct
and Month name should be picked based on the stage.
My program worked fine for picking the Name name in Month column based on the stage ID was in.
But, some of the Month's conditions are not correct, so i have pasted the excel formula used.
... View more
excel formula is doing the same thing
=IF(G4<3,IFERROR(INDEX($B$1:$F$1,MATCH(3,B4:F4,0)),IF(G4<2,IFERROR(INDEX($B$1:$D$1,MATCH(2,B4:E4,0)),""),"")),"")
... View more
Hi draycut,
this is the business rule they follow:
id=22314 is July because ID moved from 1 to 2
business rule: any ID within last six month moves from stage 1 to 2 or 3 then that Month name should be populated and till sep'19 if any ID in stage 1 and in OCT'19 if it moves to stage 2 or 3 then Month should be blank(ID=22317)
If you see from my program even after applying lag function Month condition is not applying for some scenarios
... View more
in my input data i have DATE column as:
31MAY2019
30JUN2019
31JUL2019
31AUG2019
30SEP2019
31OCT2019
these dates will be there for every ID
... View more
data temp(drop= p_id p_stage); /*temp*/ set month_fmt; by id; p_id = lag(id); p_stage = lag(stage); if p_id = id then do; if p_stage < stage then output; end; run; proc sql; create table temp1 as /*temp1*/ select id, max(stage) as stage, max(date) as Month format date9d. from temp group by id; quit; proc transpose data=month_fmt out=input1(drop= _name_); /*input1*/ by id; var Stage; id date; run; proc sql; create table input2 as select a.*,b.Month as Month /*input2*/ format = MONNAME9. from input1 as a left join temp1 as b on a.id=b.id; quit; this is the programs that i have used, i am getting the Month names but not meeting all the conditions e.g., 1 1 1 1 2 2 should be blank but i am getting Month as September 2 3 3 3 3 3 should be blank but i am getting Month as June 3 3 3 1 2 2 should be May but i am getting September please help me in solving the above issues.
... View more
we see the data for last six months only, if everything is in stage 2 then it should be blank similarly if every month is in stage 3 then it should be blank
... View more
