Help using Base SAS procedures

Sum

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Sum


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

Accepted Solutions
Solution
‎12-19-2014 04:19 PM
Regular Contributor
Posts: 180

Re: Sum

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


All Replies
Respected Advisor
Posts: 3,777

Re: Sum

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
Solution
‎12-19-2014 04:19 PM
Regular Contributor
Posts: 180

Re: Sum

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

Occasional Contributor
Posts: 8

Re: Sum

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

Contributor
Posts: 52

Re: Sum

A solution is the following:

proc sql;

  select distinct 'SUM(pmt_type='||''''||strip(pmt_type)||''''||') as '||pmt_type into Smiley Tongue_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;

Super User
Posts: 9,676

Re: Sum

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

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 298 views
  • 3 likes
  • 5 in conversation