Guys,
Need some help with a logic that i am trying to create.
The data that i am trying to work on is as below
data test;
infile datalines delimiter = '|';
input key: $5. order: 8. stage: $5. field_required : 8.;
datalines;
AA|1|A|1
AA|2|B|1
AA|3|C|1
AA|4|B|2
AA|5|D|2
AA|6|C|2
AA|7|D|3
AA|8|E|3
AB|9|B|1
AB|10|C|1
AB|11|D|1
AB|12|C|2
AB|13|D|2
AB|14|C|3
AB|15|D|3
;
RUN;
I am trying to derive the Field_required column . My logic is as follows.
I wish to find out the flow of my stage variable for each key.
In the key AA the first 3 stage A,B,C are distinct hence they get the value 1,
next the stage B repeats hence it get the Value 2 .
Now whatever follows B will get the value 2 until a repetition of stage is found under the value 2 . EX: Stage D occurs in value 2 hence the New stage D gets the value 3 and so on.
Note: In the Table you would see that the stage C (i.e order 7) gets a value 2 even though it is present in Value 1 this is because we only look at repetitions in the current value and not in previous values
Note: the ORDER variable would always be distinct.
I did try using the below macro to solve this. But i am sure that there is a simpler way of doing this.
If someone could Please Guide
%macro test(case);
proc sql;
select count(distinct order) into : cnt
from test
where KEY = "&case";
run;
%let cnt = %cmpres(&cnt);
proc sql;
select distinct order, stage
into : st1 -: st&cnt,
: cur1 -: cur&cnt
from test
where key= "&case"
order by order;
run;
%do i = 1 %to &cnt;
%if &i = 1 %then %do;
data test1;
set test;
format id 8.;
id = 1;
where key = "&CASE" AND order= &&st&i;
run;
%let iid = 1;
%end;
%if &i >1 %then %do;
proc sql noprint;
select count(stage)
into : check
from test1
where key = "&CASE" AND stage = "&&cur&i" and id = &iid;
run;
%if &check > 0 %then %do;
%let iid = %cmpres(&iid + 1);
%end;
data test2;
set test;
id = &iid;
where order = &&st&i;
run;
data test1;
set test1 test2;
run;
%end;
%end;
%mend;
%macro case_check;
PROC SQL NOPRINT;
SELECT COUNT(DISTINCT key)
INTO : CNTT
FROM TEST
RUN;
%LET CNTT = %CMPRES(&CNTT);
PROC SQL NOPRINT;
SELECT DISTINCT key
INTO : CAS1 -: CAS&CNTT
FROM TEST
;
RUN;
%DO J = 1 %TO &CNTT;
%test(CASE = &&CAS&J)
%if j = 1 %then %do;
DATA final;
SET TEST1;
RUN;
%end;
%if j>1 %then %do;
DATA final;
SET final TEST1;
RUN;
%END;
%END;
%MEND;
%CASE_CHECK
... View more
