BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Lijuu
Obsidian | Level 7

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? 

Lijuu_1-1675733197733.png

 

Thank you very much.

Best Regards.

Lijuu

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 903 views
  • 0 likes
  • 2 in conversation