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...
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.