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


Hi-

I am trying to add individual records and roll up to office, regional and state levels.  I was producing multiple table frequencies but the totals are appearing on the right hand side when I am wanting the totals to be within the table.  My data looks like this and I've also included my desired results.   The regional level rows are the sums of individual office levels.   Any help would be appreciated.   Thank you.

IDRACEGENDERPMT_TYPEICDVISITOFFICE
AAHMHMO290IN9
ABHMHMO290OUT9
ACHMFFS290IN9
ADHMMC290IN9
BAHFHMO290OUT9
BBHFFFS290IN9
BCHFPPO290IN9
BDHFMC290IN9
CAWMHMO290OUT9
CBWMFFS290IN9
CCWMPPO290IN9
CDWMMC290OUT9
CDWMMC290OUT9
DAWFHMO290OUT9
DBWFFFS290IN9
DCWFPPO290OUT9
DDWFMC290OUT9
ZAHMHMO290IN8
ZBWFHMO290IN8
ZCWFHMO290OUT8

Desired output:
LEVELRACEGENDERHMOFFSPPOMCICD290IN-VISITOUT-VISIT
OFFICE9HM2101421
OFFICE9HF1111431
OFFICE9WM1112523
OFFICE9WF1111413
OFFICE8WF1000110
OFFICE8WF1000101
REGIONHM2101421
REGIONHF1111431
REGIONWM1112523
REGIONWF3111624
1 ACCEPTED SOLUTION

Accepted Solutions
CTorres
Quartz | Level 8

try this:

data have;
input ID $ RACE $ GENDER $ PMT_TYPE $ ICD $ VISIT $ OFFICE $;
office=cats('OFFICE',office);
cards;
AA H M HMO 290 IN 9
AB H M HMO 290 OUT 9
AC H M FFS 290 IN 9
AD H M MC 290 IN 9
BA H F HMO 290 OUT 9
BB H F FFS 290 IN 9
BC H F PPO 290 IN 9
BD H F MC 290 IN 9
CA W M HMO 290 OUT 9
CB W M FFS 290 IN 9
CC W M PPO 290 IN 9
CD W M MC 290 OUT 9
CD W M MC 290 OUT 9
DA W F HMO 290 OUT 9
DB W F FFS 290 IN 9
DC W F PPO 290 OUT 9
DD W F MC 290 OUT 9
ZA H M HMO 290 IN 8
ZB W F HMO 290 IN 8
ZC W F HMO 290 OUT 8
;
run;

options missing=0;
proc freq data=have noprint;
  tables office*race*gender*pmt_type / out=freq_pmt nopercent;
  tables office*race*gender*icd / out=freq_icd nopercent;
  tables office*race*gender*visit / out=freq_visit nopercent;
run;
proc transpose data=freq_pmt out=trans_pmt(drop=_name_ _Label_);
  by office race gender;
  id pmt_type;
  var count;
run;
proc transpose data=freq_icd out=trans_icd(drop=_name_ _Label_) prefix=ICD;
  by office race gender;
  id icd;
  var count;
run;
proc transpose data=freq_visit out=trans_visit(drop=_name_ _Label_) suffix=_VISIT;
  by office race gender;
  id visit;
  var count;
run;
data want;
  merge trans_pmt trans_icd trans_visit;
  by office race gender;
run;

CTorres

View solution in original post

5 REPLIES 5
data_null__
Jade | Level 19

This is close.

data office;
   infile cards expandtabs;
  
input (ID  RACE  GENDER   PMT_TYPE ICD   VISIT OFFICE)($);
   cards;
AA H  M  HMO   290   IN 9
AB H  M  HMO   290   OUT   9
AC H  M  FFS   290   IN 9
AD H  M  MC 290   IN 9
BA H  F  HMO   290   OUT   9
BB H  F  FFS   290   IN 9
BC H  F  PPO   290   IN 9
BD H  F  MC 290   IN 9
CA W  M  HMO   290   OUT   9
CB W  M  FFS   290   IN 9
CC W  M  PPO   290   IN 9
CD W  M  MC 290   OUT   9
CD W  M  MC 290   OUT   9
DA W  F  HMO   290   OUT   9
DB W  F  FFS   290   IN 9
DC W  F  PPO   290   OUT   9
DD W  F  MC 290   OUT   9
ZA H  M  HMO   290   IN 8
ZB W  F  HMO   290   IN 8
ZC W  F  HMO   290   OUT   8
;;;;
   run;
proc print;
  
run;
options missing=0;
proc report nowd list;
  
columns office race gender pmt_type icd visit;
   define office / group descending;
  
define race / group;
  
define gender / group descending;
  
define pmt_type / across order=data;
   define icd / across;
  
define visit / across;
  
run;

12-19-2014 2-36-52 PM.png
CTorres
Quartz | Level 8

try this:

data have;
input ID $ RACE $ GENDER $ PMT_TYPE $ ICD $ VISIT $ OFFICE $;
office=cats('OFFICE',office);
cards;
AA H M HMO 290 IN 9
AB H M HMO 290 OUT 9
AC H M FFS 290 IN 9
AD H M MC 290 IN 9
BA H F HMO 290 OUT 9
BB H F FFS 290 IN 9
BC H F PPO 290 IN 9
BD H F MC 290 IN 9
CA W M HMO 290 OUT 9
CB W M FFS 290 IN 9
CC W M PPO 290 IN 9
CD W M MC 290 OUT 9
CD W M MC 290 OUT 9
DA W F HMO 290 OUT 9
DB W F FFS 290 IN 9
DC W F PPO 290 OUT 9
DD W F MC 290 OUT 9
ZA H M HMO 290 IN 8
ZB W F HMO 290 IN 8
ZC W F HMO 290 OUT 8
;
run;

options missing=0;
proc freq data=have noprint;
  tables office*race*gender*pmt_type / out=freq_pmt nopercent;
  tables office*race*gender*icd / out=freq_icd nopercent;
  tables office*race*gender*visit / out=freq_visit nopercent;
run;
proc transpose data=freq_pmt out=trans_pmt(drop=_name_ _Label_);
  by office race gender;
  id pmt_type;
  var count;
run;
proc transpose data=freq_icd out=trans_icd(drop=_name_ _Label_) prefix=ICD;
  by office race gender;
  id icd;
  var count;
run;
proc transpose data=freq_visit out=trans_visit(drop=_name_ _Label_) suffix=_VISIT;
  by office race gender;
  id visit;
  var count;
run;
data want;
  merge trans_pmt trans_icd trans_visit;
  by office race gender;
run;

CTorres

BaileyY
Obsidian | Level 7

thank you very much.  This produced a much closer output I was looking for.  Thank you!!!

billfish
Quartz | Level 8

A solution is the following:

proc sql;

  select distinct 'SUM(pmt_type='||''''||strip(pmt_type)||''''||') as '||pmt_type into :p_type separated by ',' from have;

  select distinct 'SUM(ICD='||''''||strip(ICD)||''''||') as '||COMPRESS('ICD'||ICD) into :i_type separated by ',' from have;

  select distinct 'SUM(VISIT='||''''||strip(VISIT)||''''||') as '||COMPRESS(''''||VISIT||'-VISIT''n') into :v_type separated by ',' from have;

  create table want(rename=(off=office)) as
  select compress('Office'||put(office,2.)) as off, race, gender,
         &p_type , &i_type , &v_type
  from have
  group by office, race, gender
  order by office desc, race, gender desc;


  create table want2 as
  select 'REGION' as office, race, gender,
         &p_type , &i_type , &v_type
  from have
  group by race, gender
  order by race, gender desc;

  insert into want select * from want2;
  drop table want2; 
quit;

Ksharp
Super User

SQL can give you less code and faster speed . I am about to make a paper about this when you are building a report .


data office;
   infile cards expandtabs; 
   input (ID  RACE  GENDER   PMT_TYPE ICD   VISIT OFFICE)($);
   cards; 
AA H  M  HMO   290   IN 9
AB H  M  HMO   290   OUT   9
AC H  M  FFS   290   IN 9
AD H  M  MC 290   IN 9
BA H  F  HMO   290   OUT   9
BB H  F  FFS   290   IN 9
BC H  F  PPO   290   IN 9
BD H  F  MC 290   IN 9
CA W  M  HMO   290   OUT   9
CB W  M  FFS   290   IN 9
CC W  M  PPO   290   IN 9
CD W  M  MC 290   OUT   9
CD W  M  MC 290   OUT   9
DA W  F  HMO   290   OUT   9
DB W  F  FFS   290   IN 9
DC W  F  PPO   290   OUT   9
DD W  F  MC 290   OUT   9
ZA H  M  HMO   290   IN 8
ZB W  F  HMO   290   IN 8
ZC W  F  HMO   290   OUT   8
;;;;
   run;

proc sql;
 create table temp as
  select office,race,gender,PMT_TYPE as type ,count(*) as n 
   from office
    group by office,race,gender,PMT_TYPE
 union
  select office,race,gender,ICD as type ,count(*) as n 
   from office
    group by office,race,gender,ICD
 union
  select office,race,gender,VISIT as type ,count(*) as n 
   from office
    group by office,race,gender,VISIT ;
quit;
proc transpose data=temp out=temp1(drop=_:);
 by  office race gender     ;
 id type;
 var n;
run;
proc stdize data=temp1 out=want missing=0 reponly;run;



Xia Keshan

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1016 views
  • 3 likes
  • 5 in conversation