I have been trying (unsuccessfully) to create a variable that counts upward the number of periods a company is doing business in a particular market, which starts over at 1 when sales fall to $0, or when the market changes for the firm, or when firms change. Any help would be appreciated. The count variable below is what I am shooting for.
Firm Market Sales Count
1 100 $354 1
1 100 $344 2
1 100 $244 3
1 100 $37 4
1 100 $0 .
1 200 $98 1
1 200 $99 2
1 200 $0 3
1 200 $34 1
1 200 $25 2
2 100 $0 .
2 100 $34 1
2 100 $44 2
2 100 $39 3
2 100 $0 .
2 200 $98 1
2 200 $99 2
2 200 $23 3
2 200 $34 4
2 200 $25 5
3 200 $9 1
3 200 $9 2
3 200 $2 3
3 200 $3 4
3 200 $2 5
data have;
input Firm :$ Market :$ Sales :dollar8. ;
cards;
1 100 $354
1 100 $344
1 100 $244
1 100 $37
1 100 $0
1 200 $98
1 200 $99
1 200 $0
1 200 $34
1 200 $25
2 100 $0
2 100 $34
2 100 $44
2 100 $39
2 100 $0
2 200 $98
2 200 $99
2 200 $23
2 200 $34
2 200 $25
3 200 $9
3 200 $9
3 200 $2
3 200 $3
3 200 $2
;
data want;
set have;
by firm market notsorted;
if first.market then call missing (count);
count+1;
if sales=0 then call missing(count);
run;
Haikuo
data have;
input Firm :$ Market :$ Sales :dollar8. ;
cards;
1 100 $354
1 100 $344
1 100 $244
1 100 $37
1 100 $0
1 200 $98
1 200 $99
1 200 $0
1 200 $34
1 200 $25
2 100 $0
2 100 $34
2 100 $44
2 100 $39
2 100 $0
2 200 $98
2 200 $99
2 200 $23
2 200 $34
2 200 $25
3 200 $9
3 200 $9
3 200 $2
3 200 $3
3 200 $2
;
data want;
set have;
by firm market notsorted;
if first.market then call missing (count);
count+1;
if sales=0 then call missing(count);
run;
Haikuo
Thanks,
That worked perfectly...
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.