counting many variables by group problem

[moteku]

 From the table above, I would like to count number of successful and unsuccessful (failed and canceled state) kickstarter campaigns by main category.

I need to have table like this:

main_category | number_of_successful_campaigns | number_of_unsuccessful_campaigns

          x             |                              x                          |                             x

          y             |                              x                          |                             x

I do not know how should I even start.. 

[yabwon · Accepted solution]

1) Next time share some useful test data...

2) try this:

data have;
input id main_category $ state $;
cards;
1 X successful
2 Y successful
3 X failed
4 Y failed
5 X failed
6 X failed
7 Y successful
8 Y successful
;
run;
proc print;
run;

proc tabulate data = have out = want;
  class main_category state;
  table main_category, state*N;
run;

proc transpose data = want out = want(drop=_name_) prefix=number_of_ suffix=_campaigns;
  by main_category;
  id state;
  var N;
run;

proc print;
run;

Bart

[moteku]

It works but now i have to understand PROC STATEMENTS you have used. Thanks. (I will remember to share data 😇)

[yabwon]

1) Proc tabulate works as Excel's Pivot. it is just to aggregate data, you could use Proc SQL, for that job too.

2) Proc transpose is just for "reshaping" dataset.

B-)

[PaigeMiller]

proc freq data=have;
     tables main_category*state;
run;

If you want to combine failed and cancelled, you can do that with custom formats, or in a DATA step.

In the future, please provide example data in a usable form, as a SAS data step (instructions). Data in a screen capture is not usable.