Count cases within a variable and create new variable

[Lijuu]

Dear All Respected:

I have the following data that consists of three variables (ID and X1). However, I want to create a new variable (New-X1) that counts the same values of X1. Herewith I attached the example. Could you help me to give a SAS code that enables to execution of a new variable as shown in the format below? 

Thank you very much.

Best Regards.

Lijuu

[ballardw · Accepted solution]

Pictures are extremely poor ways to share data. We cannot write code against picture. At least paste text. I am too lazy to retype pictures.

Something like this should work. Untested code as you did not provide data in a usable form, did not mention the name of your existing table so I use JUNK in the code below.

proc sql;
   create table want as 
   select junk.*, count(x1) as new_x1
   from junk
   group by x1
   order by id
   ;
quit;

The Select is asking for all the records and variables from junk and requesting a count of X1 as the new variable. The Group by says how to group records for the summary function Count, and the order is to maintain the original order of the data. The output order might end up different than expected.

[Lijuu]

Dear ballardw:

Sorry for the uneditable data that I posted. Nevertheless, you did it and your code executed the expected outcome well.

Thank you very much.

[Lijuu]

Dears:

I have data in the following format:

Year    Code    Population 

2000   1001     10

2000   1001     15

2001   1001     4

2002   1001     3

2002   1001     5

2000   1002     5

2000   1002     8

2001   1002     10

2002   1002     25

2002  1002      30

I want to have a total population per year and code it as

Year  Code Population

2000   1001   25

2001   1001    4

2002   1001    8

2000   1002    13

2001   1002    10

2002   1002    55

And finally, I want to transpose the total population per year as

Code  Population_2000  Population_2001  Population_2002

1001           25                        4                             8

1002           13                       10                           55

Thank you very much for your help.

Lijalem