Column to row and count

[SuryaKiran]

I have a dataset with 6 million records with 40 measures. My dataset looks like:

state provider measure
FL AAA A
FL AAA A
FL AAA A
FL AAA B
FL AAA B
FL AAA c
FL AAA c
FL AAA c
FL AAA c
FL BBB A
FL BBB B
FL BBB C
FL BBB C
FL BBB C

I want count for the measure. My output must look like this :

[Loko · Accepted solution]

hello,

data have;
infile datalines;
input state $ provider $ measure $;
datalines;
FL AAA A
FL AAA A
FL AAA A
FL AAA B
FL AAA B
FL AAA C
FL AAA C
FL AAA C
FL AAA C
FL BBB A
FL BBB B
FL BBB C
FL BBB C
FL BBB C
;
run;

proc sort data=have;
by state provider measure;
run;

data have_int;
set have;
by state provider measure;

retain val val_tot;

if first.measure then val=1;
else val=val+1;

if first.provider then val_tot=1;
else val_tot=val_tot+1;

if last.measure then output;
if last.provider then do;val=val_tot;measure='Total';output;end;


run;

proc transpose data=have_int out=want(drop=_name_);
by state provider;
var val;
id measure;
run;

[SuryaKiran]

Thank you @Loko
This is helpful.

[Astounding]

This is untested, but should be in the ballpark:

proc tabulate data=have;

class state provider measure;

tables state*provider, measure all;

run;

[ballardw]

Do you want a row total for each state or just overall (across all states)? Since your example data only inclues one value for State is not clear what the summary row represents.