Help using Base SAS procedures

How to rank three orders within the same ID and count the number of observations for each order

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How to rank three orders within the same ID and count the number of observations for each order

Hi,

 

I have a dataset looks like below:

 

ID   Visit    CurrentTotal    

1      0             0                 

1      0             1                  

1      1             0                 

1      0             3                 

1      1             2                 

1      1             1                

1      1             0                

 

For the same ID,  there are three parts of data - the observations in black, red and blue. For each part, I need to count the total number of visits. Right now, the CurrentTotal variable is displayed in the dataset, which is not correct for the counts of each part (black, red and blue). What I would like to ask is that "How to add a variable "TargetTotal" as shown below to have the correct number of visit count for each part? How can the SAS codes be able to rank three orders within the same ID?" The order should start with 0, and end till the next 0, so there are three parts (the black, red and blue t) for the same ID.

 

ID   Visit    CurrentTotal    TargetTotal

1      0             0                 0

1      0             1                 0 

1      1             0                 1

1      0             3                 0

1      1             2                 1

1      1             1                 2

1      1             0                 3

 

 Thank you very much. Looking forward to the solutions. 

 


Accepted Solutions
Solution
Sunday
Respected Advisor
Posts: 4,640

Re: How to rank three orders within the same ID and count the number of observations for each order

Assuming each part of your data starts at Visit=0 :

 

data have;
input ID   Visit;
datalines;
1      0   
1      0   
1      1   
1      0   
1      1   
1      1   
1      1   
;

data want;
do until(last.id);
    set have; by id;
    if visit = 0 then rank = 0;
    else rank = rank + 1;
    output;
    end;
run;

proc print noobs; run;
PG

View solution in original post


All Replies
Solution
Sunday
Respected Advisor
Posts: 4,640

Re: How to rank three orders within the same ID and count the number of observations for each order

Assuming each part of your data starts at Visit=0 :

 

data have;
input ID   Visit;
datalines;
1      0   
1      0   
1      1   
1      0   
1      1   
1      1   
1      1   
;

data want;
do until(last.id);
    set have; by id;
    if visit = 0 then rank = 0;
    else rank = rank + 1;
    output;
    end;
run;

proc print noobs; run;
PG
Super User
Posts: 6,927

Re: How to rank three orders within the same ID and count the number of observations for each order

data want;
set have;
by id;
if visit = 0 then targettotal = 0;
else if first.id then targettotal = 1;
else targettotal + 1;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: How to rank three orders within the same ID and count the number of observations for each order

Thanks! It works.
Super User
Posts: 5,069

Re: How to rank three orders within the same ID and count the number of observations for each order

[ Edited ]

The simplest version would rely on VISIT only, to create TargetTotal:

 

data want;

set have;

if visit=0 then TargetTotal=0;

else TargetTotal + 1;

run;

 

Note that you don't have to worry about ID, when the first observation for each ID has VISIT=0.  The program checks for VISIT=0, which automatically includes the first observation for each ID.

New Contributor
Posts: 4

Re: How to rank three orders within the same ID and count the number of observations for each order

Thanks! It works. But when the dataset has multiple IDs, a 'by ID;' statement need to be used.
Super User
Posts: 9,662

Re: How to rank three orders within the same ID and count the number of observations for each order


data have;
input ID   Visit;
datalines;
1      0   
1      0   
1      1   
1      0   
1      1   
1      1   
1      1   
;
data temp;
 set have;
 by id;
 if first.id or visit=0 then group+1;
run;
data want;
 set temp;
 by group;
 if first.group then total=-1;
 total+1;
run;


New Contributor
Posts: 4

Re: How to rank three orders within the same ID and count the number of observations for each order

[ Edited ]

Thanks! All responses work. It is hard to decide which one is the best solution; I choose the answer with the earliest response. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 162 views
  • 2 likes
  • 5 in conversation