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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.