BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SuryaKiran
Meteorite | Level 14

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
1 ACCEPTED SOLUTION

Accepted Solutions
Loko
Barite | Level 11

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

4 REPLIES 4
Loko
Barite | Level 11

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
Meteorite | Level 14
Thank you @Loko
This is helpful.
Thanks,
Suryakiran
Astounding
PROC Star

This is untested, but should be in the ballpark:

 

proc tabulate data=have;

class state provider measure;

tables state*provider, measure all;

run;

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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