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 :
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;
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;
This is untested, but should be in the ballpark:
proc tabulate data=have;
class state provider measure;
tables state*provider, measure all;
run;
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.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.