DATA Step, Macro, Functions and more

Column to row and count

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 136
Accepted Solution

Column to row and count

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 :

Capture.PNG

 

 

 

Thanks,
Suryakiran

Accepted Solutions
Solution
‎12-16-2016 09:32 AM
Super Contributor
Posts: 308

Re: Column to row and count

Posted in reply to SuryaKiran

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;

View solution in original post


All Replies
Solution
‎12-16-2016 09:32 AM
Super Contributor
Posts: 308

Re: Column to row and count

Posted in reply to SuryaKiran

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;
Frequent Contributor
Posts: 136

Re: Column to row and count

Thank you @Loko
This is helpful.
Thanks,
Suryakiran
Super User
Posts: 5,513

Re: Column to row and count

Posted in reply to SuryaKiran

This is untested, but should be in the ballpark:

 

proc tabulate data=have;

class state provider measure;

tables state*provider, measure all;

run;

Super User
Posts: 11,343

Re: Column to row and count

Posted in reply to SuryaKiran

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 227 views
  • 1 like
  • 4 in conversation