topic Re: Flagging Values after Mark Point in SAS Programming

Flagging Values after Mark Point

kashun — Sat, 12 Dec 2020 00:25:07 GMT

I do have a dat that specific price mark for each items per id. I would like to flag the line that changes after the price mark. Have been trying hash objects but seem not t work. Need some guidance please.

Have


data have;
input @1 item @4 id @8 price_mark @13 price;
datalines;
1  234 1000 400
2  234 1000 450
3  234 1000 1000
4  234 1000 1000
5  234 1000 1000
6  234 1000 1000
7  234 1000 760
8  234 1000 430
9  234 1000 500
10 234 1000 1000
11 234 1000 1000
12 234 1000 500
13 234 1000 360
3  321 500  420
4  321 500  500
5  321 500  500
6  321 500  500
7  321 500  500
8  321 500  300
9  321 500  450
10 321 500  300
;
run;

Want

item	id	price mark	price	flag
1	234	1000	400
2	234	1000	450
3	234	1000	1000
4	234	1000	1000
5	234	1000	1000
6	234	1000	1000
7	234	1000	760	1
8	234	1000	430	2
9	234	1000	500	3
10	234	1000	1000
11	234	1000	1000
12	234	1000	500	1
13	234	1000	360	2
3	321	500	420
4	321	500	500
5	321	500	500
6	321	500	500
7	321	500	500
8	321	500	300	1
9	321	500	450	2
10	321	500	300	3

Re: Flagging Values after Mark Point

ChrisNZ — Sat, 12 Dec 2020 01:18:12 GMT

Like this?

data WANT;
  set HAVE;
  by ID;
  if first.ID then call missing(MATCHED,FLAG);
  if PRICE_MARK=PRICE then MATCHED+1;
  if MATCHED then do;
    if PRICE_MARK=PRICE then FLAG=.; else FLAG+1;
  end;
run;

item	id	price_mark	price	FLAG
1	234	1000	400	.
2	234	1000	450	.
3	234	1000	1000	.
4	234	1000	1000	.
5	234	1000	1000	.
6	234	1000	1000	.
7	234	1000	760	1
8	234	1000	430	2
9	234	1000	500	3
10	234	1000	1000	.
11	234	1000	1000	.
12	234	1000	500	1
13	234	1000	360	2
3	321	500	420	.
4	321	500	500	.
5	321	500	500	.
6	321	500	500	.
7	321	500	500	.
8	321	500	300	1
9	321	500	450	2
10	321	500	300	3

Re: Flagging Values after Mark Point

hhinohar — Sat, 12 Dec 2020 02:09:59 GMT

Not hash object but here goes. Hash object is not a magic tool that works for everything..

data have;
	input @1 item @4 id @8 price_mark @13 price;
	datalines;
1  234 1000 400
2  234 1000 450
3  234 1000 1000
4  234 1000 1000
5  234 1000 1000
6  234 1000 1000
7  234 1000 760
8  234 1000 430
9  234 1000 500
10 234 1000 1000
11 234 1000 1000
12 234 1000 500
13 234 1000 360
3  321 500  420
4  321 500  500
5  321 500  500
6  321 500  500
7  321 500  500
8  321 500  300
9  321 500  450
10 321 500  300
;
run;

data want;
  set have;
  by id;
  *flag of price_mark and price difference;
  flg=(price_mark ne price);
  *count if flg changes;
  if flg ne lag(flg) then
    cf+1;
  *reset counter if first.id;
  if first.id then
    cf=1;
  *drop the first flg;
  if flg=1 and cf=1 then
    flag=.;
  *count each flg;
  else if flg=1 then
    flag+1;
  *else missing;
  else
    flag=.;
drop flg cf;
run;

Re: Flagging Values after Mark Point

kashun — Mon, 14 Dec 2020 15:06:00 GMT

Thanks very much @ChrisNZ. This looks simpler than what I came up with.

Re: Flagging Values after Mark Point

kashun — Mon, 14 Dec 2020 15:08:17 GMT

Thanks very much @hhinoha. Totally agree with you on Hash objects. I did tried that and didn't work.