topic Count cases within a variable and create new variable in SAS Procedures

Count cases within a variable and create new variable

Lijuu — Tue, 07 Feb 2023 01:27:21 GMT

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

Re: Count cases within a variable and create new variable

ballardw — Tue, 07 Feb 2023 05:50:41 GMT

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.

Re: Count cases within a variable and create new variable

Lijuu — Tue, 07 Feb 2023 07:50:01 GMT

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.

Re: Count cases within a variable and create new variable

Lijuu — Mon, 03 Apr 2023 04:08:38 GMT

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