the number of occurrences of the variable in a given year SAS

[moteku]

Let's assume that i have data like this:

data have;
input id category $ year;
cards;
1 X 2009
2 U 2009
3 X 2009
4 Y 2010
5 Y 2010
6 Z 2011
7 Z 2011
8 X 2012
9 U 2013
10 U 2013
;
run;

Like in subject, I would like to establish the number of occurrences of the category in a given year.

I tried on my data to use proc tabulate:

proc tabulate data = have out = want;
    class year category;
    table year category*N;
run;

Ang got something like this: 

 Next i tried proc transpose:

proc transpose data=want out data=want;
   by year;
   id category;
   var N;
run;

But it didint help:

 

What have I done wrong?

[PaigeMiller]

Obviously, you didn't run this code, as data set HAVE is not created because of a coding error. Please provide us with correct working code, you will get faster help that way.

Nevertheless, I think this works. I have not tested it because your code to create data set HAVE doesn't work. In order for this to work, variable LAUNCHED must be numeric and a valid SAS data value — it can't be character.

proc report data=have; 
    columns category launched;
    define category/group;
    define launched/across format=year.;
run;

[moteku]

I've corrected the question

[PaigeMiller]

---

@moteku wrote:
I've corrected the question

---

So the code I wrote should work on your corrected data if you remove FORMAT=YEAR.

[Ksharp]

@StatDave 

[Patrick · Accepted solution]

Not sure if I understand but are you asking for something as simple as below?

If so then the main thing that's missing in your code is a comma in the table statement after variable year - used to separate the x from the y axis.

data have;
  input id category $ year;
  cards;
1 X 2009
2 U 2009
3 X 2009
4 Y 2010
5 Y 2010
6 Z 2011
7 Z 2011
8 X 2012
9 U 2013
10 U 2013
;

proc format;
  value missasblank
    .=' '
    other=[best16.]
    ;
run;
proc tabulate data = have out = want;
    class year category;
    keylabel n=' ';
    table year, category*N*f=missasblank.;
run;

[moteku]

Works excactly how I want, thanks really ❤️

[ballardw]

No need for the format in this case for the missing values. The Tabulate table option MISSTEXT will suppress the . for the N statistic:

proc tabulate data = have out = want;
    class year category;
    keylabel n=' ';
    table year, category*N
       / misstext=' ';
run;

---

@Patrick wrote:

Not sure if I understand but are you asking for something as simple as below?

If so then the main thing that's missing in your code is a comma in the table statement after variable year - used to separate the x from the y axis.

data have;
  input id category $ year;
  cards;
1 X 2009
2 U 2009
3 X 2009
4 Y 2010
5 Y 2010
6 Z 2011
7 Z 2011
8 X 2012
9 U 2013
10 U 2013
;

proc format;
  value missasblank
    .=' '
    other=[best16.]
    ;
run;
proc tabulate data = have out = want;
    class year category;
    keylabel n=' ';
    table year, category*N*f=missasblank.;
run;

---