topic Column to row and count in SAS Programming

Column to row and count

SuryaKiran — Mon, 14 Nov 2016 15:14:50 GMT

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 :

Re: Column to row and count

Loko — Mon, 14 Nov 2016 15:41:49 GMT

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;

Re: Column to row and count

Astounding — Mon, 14 Nov 2016 15:56:15 GMT

This is untested, but should be in the ballpark:

proc tabulate data=have;

class state provider measure;

tables state*provider, measure all;

run;

Re: Column to row and count

ballardw — Wed, 16 Nov 2016 22:05:14 GMT

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.

Re: Column to row and count

SuryaKiran — Fri, 16 Dec 2016 14:32:14 GMT

Thank you @Loko
This is helpful.