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.
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;
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;
data want;
set have;
by id;
if visit = 0 then targettotal = 0;
else if first.id then targettotal = 1;
else targettotal + 1;
run;
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.
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;
Thanks! All responses work. It is hard to decide which one is the best solution; I choose the answer with the earliest response.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.