topic Re: Generate new count variable based on existing variable in dataset in SAS Programming

Generate new count variable based on existing variable in dataset

Epi_Stats — Tue, 29 Oct 2024 21:10:08 GMT

Hi,

I have a large dataset with a summary of each respondent’s favourite fruit combination at different times of the day (breakfast, lunch, dinner):

data have;
input ID time $ fruit :$30.;
datalines; 
1 bfast apple-orange
1 bfast apple-orange
1 dinner apple-orange
2 bfast pear-peach
2 dinner pear-peach
3 bfast banana-mango
3 lunch banana-mango
3 lunch banana-mango
4 lunch kiwi-guava
4 dinner kiwi-guava
;
run;

I want to create a new variable “count” based on each fruit combination – I include an example output below of what I want. This variable does NOT need to be based on the unique ID – for example, apple-orange would have a count range 1-4, even though ID 1 and 2 share this combination.

data want;
input ID time $ fruit :$30. count;
datalines; 
1 bfast apple-orange 1
1 bfast apple-orange 2
1 dinner apple-orange 3
2 bfast apple-orange 4
2 dinner pear-peach 1
3 bfast banana-mango 1
3 lunch banana-mango 2
3 lunch banana-mango 3
4 lunch kiwi-guava 1
4 dinner kiwi-guava 2
;
run;

I thought I could use a do loop to achieve this, but I’m struggling to think how to do this in SAS, and would be very grateful for any suggestions/help!

Thank you in advance,

Re: Generate new count variable based on existing variable in dataset

PaigeMiller — Tue, 29 Oct 2024 22:19:28 GMT

I am confused by your problem statement. I don't see how apple-orange gets a "count range" of 1 to 4 from the original data, when apple-orange appears 3 times. Please explain.

However, if I may guess, you can sort the data by the fruit and then obtain a count like this:

proc sort data=have;
    by fruit;
run;

data want;
    set have;
    by fruit;
    if first.fruit then count=0;
    count+1;
run;

Re: Generate new count variable based on existing variable in dataset

Epi_Stats — Tue, 29 Oct 2024 22:49:48 GMT

Thank you, @PaigeMiller - this is precisely what I was looking for here! Just in response to your query, since I'm not counting fruit by ID, apple-orange count range is 1-4 -- sorry, I probably could have explained this better in my original post.

Thank you again for your help!

Re: Generate new count variable based on existing variable in dataset

PaigeMiller — Wed, 30 Oct 2024 09:49:06 GMT

@Epi_Stats wrote:

Thank you, @PaigeMiller - this is precisely what I was looking for here! Just in response to your query, since I'm not counting fruit by ID, apple-orange count range is 1-4 -- sorry, I probably could have explained this better in my original post.

While I am glad to solve your problem, this explanation explains nothing. There are not 4 apple-orange in your data. When you provide a desired answer, it should be consistent with the input data you provide. Providing desired output that is consistent with the input data on future questions will help you get faster and better answers. You got lucky on this question, I guessed right as to what you wanted, but I (and others) don't always guess right.