- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 01-25-2021 04:22 PM
(2675 views)
Hi all,
Would you mind let me know what your thoughts are if I want to count for below dataset: column id, order, and type are the dataset I currently have, and column count is the dataset I want to create. Basically, I want to count the type variable within each id following the order, i.e., if the one type got interrupted by another type, then the second part of the same type need to be re-counted for this id.
id | order | type | count |
1 | 1 | Not_Applicable | 1 |
1 | 2 | Not_Applicable | 2 |
1 | 3 | Not_Applicable | 3 |
1 | 4 | Not_Applicable | 4 |
1 | 5 | Not_Applicable | 5 |
1 | 6 | Not_Applicable | 6 |
1 | 7 | Not_Applicable | 7 |
1 | 8 | Not_Applicable | 8 |
1 | 9 | Not_Applicable | 9 |
1 | 10 | Not_Applicable | 10 |
1 | 11 | Not_Applicable | 11 |
1 | 12 | Not_Applicable | 12 |
1 | 13 | Not_Applicable | 13 |
1 | 14 | Not_Applicable | 14 |
1 | 15 | Not_Applicable | 15 |
1 | 16 | Not_Applicable | 16 |
1 | 17 | Not_Applicable | 17 |
1 | 18 | Not_Applicable | 18 |
1 | 19 | intermittent | 1 |
1 | 20 | Not_Applicable | 1 |
2 | 1 | Not_Applicable | 1 |
2 | 2 | Not_Applicable | 2 |
2 | 3 | Not_Applicable | 3 |
2 | 4 | Not_Applicable | 4 |
2 | 5 | Not_Applicable | 5 |
2 | 6 | Not_Applicable | 6 |
2 | 7 | Not_Applicable | 7 |
2 | 8 | Not_Applicable | 8 |
2 | 9 | Not_Applicable | 9 |
2 | 10 | Not_Applicable | 10 |
2 | 11 | Not_Applicable | 11 |
2 | 12 | Not_Applicable | 12 |
2 | 13 | Not_Applicable | 13 |
2 | 14 | Discontinued | 1 |
2 | 15 | Discontinued | 2 |
2 | 16 | Discontinued | 3 |
2 | 17 | Discontinued | 4 |
2 | 18 | Discontinued | 5 |
2 | 19 | Discontinued | 6 |
2 | 20 | Discontinued | 7 |
3 | 1 | Not_Applicable | 1 |
3 | 2 | Not_Applicable | 2 |
3 | 3 | Not_Applicable | 3 |
3 | 4 | Not_Applicable | 4 |
3 | 5 | Not_Applicable | 5 |
3 | 6 | Not_Applicable | 6 |
3 | 7 | Not_Applicable | 7 |
3 | 8 | Not_Applicable | 8 |
3 | 9 | Not_Applicable | 9 |
3 | 10 | Not_Applicable | 10 |
3 | 11 | intermittent | 1 |
3 | 12 | intermittent | 2 |
3 | 13 | intermittent | 3 |
3 | 14 | intermittent | 4 |
3 | 15 | intermittent | 5 |
3 | 16 | Not_Applicable | 1 |
3 | 17 | Not_Applicable | 2 |
3 | 18 | Not_Applicable | 3 |
3 | 19 | Not_Applicable | 4 |
3 | 20 | Not_Applicable | 5 |
4 REPLIES 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
See if this come close:
data want; set have; by idordertype notsorted; retain count; if first.id or first.type the count=1; else count+1; run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
no, it's not working, i got count = 1 for every row.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@yahoo0806 wrote:
no, it's not working, i got count = 1 for every row.
Try with out ORDER on the BY statement.
Couldn't test the code as no data step supplied and too lazy at the moment to turn your "data" into one.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This should do it.
data have;
input id order type $20.;
datalines;
1 1 Not_Applicable
1 2 Not_Applicable
1 3 Not_Applicable
1 4 Not_Applicable
1 5 Not_Applicable
1 6 Not_Applicable
1 7 Not_Applicable
1 8 Not_Applicable
1 9 Not_Applicable
1 10 Not_Applicable
1 11 Not_Applicable
1 12 Not_Applicable
1 13 Not_Applicable
1 14 Not_Applicable
1 15 Not_Applicable
1 16 Not_Applicable
1 17 Not_Applicable
1 18 Not_Applicable
1 19 intermittent
1 20 Not_Applicable
2 1 Not_Applicable
2 2 Not_Applicable
2 3 Not_Applicable
2 4 Not_Applicable
2 5 Not_Applicable
2 6 Not_Applicable
2 7 Not_Applicable
2 8 Not_Applicable
2 9 Not_Applicable
2 10 Not_Applicable
2 11 Not_Applicable
2 12 Not_Applicable
2 13 Not_Applicable
2 14 Discontinued
2 15 Discontinued
2 16 Discontinued
2 17 Discontinued
2 18 Discontinued
2 19 Discontinued
2 20 Discontinued
3 1 Not_Applicable
3 2 Not_Applicable
3 3 Not_Applicable
3 4 Not_Applicable
3 5 Not_Applicable
3 6 Not_Applicable
3 7 Not_Applicable
3 8 Not_Applicable
3 9 Not_Applicable
3 10 Not_Applicable
3 11 intermittent
3 12 intermittent
3 13 intermittent
3 14 intermittent
3 15 intermittent
3 16 Not_Applicable
3 17 Not_Applicable
3 18 Not_Applicable
3 19 Not_Applicable
3 20 Not_Applicable
;
data want;
set have;
by id type notsorted;
if first.type then count = 0;
count + 1;
run;