topic Re: counting many variables by group problem in SAS Programming

counting many variables by group problem

moteku — Tue, 04 Jan 2022 10:37:59 GMT

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..

Re: counting many variables by group problem

yabwon — Tue, 04 Jan 2022 10:45:04 GMT

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

Re: counting many variables by group problem

moteku — Tue, 04 Jan 2022 10:55:19 GMT

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

Re: counting many variables by group problem

yabwon — Tue, 04 Jan 2022 11:00:05 GMT

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-)

Re: counting many variables by group problem

PaigeMiller — Tue, 04 Jan 2022 11:23:03 GMT

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.