Creating sequence variable with partitioning

smeeker — Mon, 21 Oct 2024 16:15:22 GMT

I am working with a dataset that has vaccine administration dates and repeats the entire row each time there is a new vaccine. I'm trying to analyze the data, but it's becoming difficult when I have 5 rows with all of the same info except for the vaccine_admin_date. I am wanting to create a sequence variable that starts with 1 and restarts its counting with each new 'id'. Unfortunately, there is no "partition by" in proc sql - I can't seem to find a solution.

HAVE
id	vaccine_admin_date
1	7/1/2024
1	8/1/2024
1	9/1/2024
1	10/1/2024
2	1/2/2024
2	2/2/2024
2	3/2/2024
2	4/2/2024

WANT
id	vaccine_admin_date	vaccine_sequence
1	7/1/2024	1
1	8/1/2024	2
1	9/1/2024	3
1	10/1/2024	4
2	1/2/2024	1
2	2/2/2024	2
2	3/2/2024	3
3	5/4/2024	1

Re: Creating sequence variable with partitioning

PaigeMiller — Mon, 21 Oct 2024 16:17:31 GMT

SQL is not the best tool here.

data want;
    set have;
    by id;
    if first.id then vaccine_sequence=0;
    vaccine_sequence+1;
run;

Re: Creating sequence variable with partitioning

smeeker — Mon, 21 Oct 2024 16:38:45 GMT

Thank you, this was a lot less complicated than I was making it. Appreciate it!

topic Re: Creating sequence variable with partitioning in SAS Programming

Creating sequence variable with partitioning

Re: Creating sequence variable with partitioning

Re: Creating sequence variable with partitioning