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 |
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 |
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 |
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.