Prev: Promote Business by Quality Advertising!
Next: User written macro or SAS supplied macro ??
From: Mary on 30 Dec 2009 15:19
Hi, all,

I'd appreciate any ideas on making code like the below not so tedious.

This is a generic program below, but one we are constantly dealing with, so I'd appreciate some input.

Our problem is what to do when different columns on the same report operate on different subsets of the data, and thus one proc tabulate cannot run the full report.

The solution below works; i.e. save each part into a data set, then merge together for a final report, but this approach strikes me as very long. For instance, below I want to get the number of people who switched from one brand to another- in the example we are looking at VW Golf users and what car they switched to after owning a VW Golf, and we want that by the variable new (1= first VW Golf or Rabbit owned, 0= previous VW Golf or Rabbit owned), and then we look at the next car purchased.

Here we have columns for all users, then **on the same report**, have a subset of users (don't include those who discontinue- don't buy a new car, or do not switch- buy a VW Golf again), with those range of columns as well.

This code works, but winds up being very long- can anybody think of some way to do this that gets everything into one data set (and therefore onto one report), but doesn't take as much code?

Again, this is a made up report, but we do many reports like this (i.e, the columns on the report sometimes are off different subsets of the data), and would appreciate any ideas as to how to code these more efficiently.

-Mary

data all_data;
informat switch_to $14. new 4.;
infile cards missover;
input switch_to 1-14 new 15-15;
cards;
VW JETTA 1
TOYOTA CAMREY 0
VW TOUREG 0
TOYOTA PRIUS 0
TOYOTA PRIUS 0
HONDA CIVIC 1
Discontinue 0
Discontinue 1
Discontinue 0
No Switch 1
No Switch 1
run;

ods output table=table1;
proc tabulate data=work.all_data;
class switch_to new;
table
switch_to all,
new*(N*f=7.0 PCTN<switch_to all>*F=8.1)
(N*f=7.0 PCTN<switch_to all>*F=8.1)
/RTS=40 MISSTEXT=' ' BOX={label="Label"} ROW=FLOAT;
keylabel n='n' pctn='%';
keyword n pctn/style=[background=lightgrey just=c];
run;

data vwgolf_new;
set table1;
if new=1;
pct=PctN_01;
if _type_='01' then switch_to='zz. Total';
keep switch_to n pct;
format pct 7.1;
run;

data vwgolf_existing;
set table1;
if new=0;
pct=PctN_01;
if _type_='01' then switch_to='zz. Total';
keep switch_to n pct;
format pct 7.1;
run;

data vwgolf_all;
set table1;
if new=.;
pct=PctN_00;
if _type_='00' then switch_to='zz. Total';
keep switch_to n pct;
format pct 7.1;
run;

proc sql noprint;
create table base as
select distinct switch_to from all_data
order by switch_to;
quit;

proc sql noprint;
insert into base values('zz. Total');
quit;

proc sql noprint;
create table base2 as
select base.switch_to,
vwgolf_new.n as vwgolf_new_n,
vwgolf_new.pct as vwgolf_new_pct
from base
left outer join
vwgolf_new
on base.switch_to=vwgolf_new.switch_to
order by base.switch_to;
quit;

proc sql noprint;
create table base3 as
select base2.*,
vwgolf_existing.n as vwgolf_existing_n,
vwgolf_existing.pct as vwgolf_existing_pct
from base2
left outer join
vwgolf_existing
on base2.switch_to=vwgolf_existing.switch_to
order by base2.switch_to;
quit;

proc sql noprint;
create table base4 as
select base3.*,
vwgolf_all.n as vwgolf_all_n,
vwgolf_all.pct as vwgolf_all_pct
from base3
left outer join
vwgolf_all
on base3.switch_to=vwgolf_all.switch_to
order by base3.switch_to;
quit;


ods output table=table2;
proc tabulate data=work.all_data;
class switch_to new;
table
switch_to all,
new*(N*f=7.0 PCTN<switch_to all>*F=8.1)
(N*f=7.0 PCTN<switch_to all>*F=8.1)
/RTS=40 MISSTEXT=' ' BOX={label="Label"} ROW=FLOAT;
keylabel n='n' pctn='%';
keyword n pctn/style=[background=lightgrey just=c];
where trim(switch_to) not in ('No Switch','Discontinue');
run;

data vwgolf_new_switchers;
set table2;
if new=1;
pct=PctN_01;
if _type_='01' then switch_to='zz. Total';
keep switch_to n pct;
format pct 7.1;
run;

data vwgolf_existing_switchers;
set table2;
if new=0;
pct=PctN_01;
if _type_='01' then switch_to='zz. Total';
keep switch_to n pct;
format pct 7.1;
run;

data vwgolf_all_switchers;
set table2;
if new=.;
pct=PctN_00;
if _type_='00' then switch_to='zz. Total';
keep switch_to n pct;
format pct 7.1;
run;

proc sql noprint;
create table base5 as
select base4.*,
vwgolf_new_switchers.n as vwgolf_new_switchers_n,
vwgolf_new_switchers.pct as vwgolf_new_switchers_pct
from base4
left outer join
vwgolf_new_switchers
on base4.switch_to=vwgolf_new_switchers.switch_to
order by base4.switch_to;
quit;

proc sql noprint;
create table base6 as
select base5.*,
vwgolf_existing_switchers.n as vwgolf_existing_switchers_n,
vwgolf_existing_switchers.pct as vwgolf_existing_switchers_pct
from base5
left outer join
vwgolf_existing_switchers
on base5.switch_to=vwgolf_existing_switchers.switch_to
order by base5.switch_to;
quit;

proc sql noprint;
create table base7 as
select base6.*,
vwgolf_all_switchers.n as vwgolf_all_switchers_n,
vwgolf_all_switchers.pct as vwgolf_all_switchers_pct
from base6
left outer join
vwgolf_all_switchers
on base6.switch_to=vwgolf_all_switchers.switch_to
order by base6.switch_to;
quit;

proc contents varnum data=base7;
run;


ods listing close;
ods tagsets.excelxp file='C:\Documents and Settings\mhowa14\Work_Activities\report_example\vwgolf_switchers_2009_12_30.xml'
style=style1
options(default_column_width='20,20,10,10,10,10,10,10,10,10,10,10,10,10,10'
sheet_label=' ' autofit_height="yes"
ORIENTATION = 'landscape'
center_horizontal = 'yes'
center_vertical = 'no'
PRINT_FOOTER = "run 12/30/2009");

ods tagsets.excelxp options(sheet_name="vwgolf_index2007");
title1 "vwgolf Switching Patterns Index Period 10/1/2007 to 12/31/2007";
title2 " ";
proc report data=base7 nowindows;
column
("Switch To" switch_to)
("All Surveys" vwgolf_new_n vwgolf_new_pct vwgolf_existing_n vwgolf_existing_pct vwgolf_all_n vwgolf_all_pct)
("Switchers Only" vwgolf_new_switchers_n vwgolf_new_switchers_pct
vwgolf_existing_switchers_n vwgolf_existing_switchers_pct
vwgolf_all_switchers_n vwgolf_all_switchers_pct);

define switch_to/order " " style(column)=[tagattr='format:#,##0' just=l] style(header)=[background=lightgrey Just=l];

define vwgolf_new_n/display "New N" style(column)=[tagattr='format:#,##0' just=R] style(header)=[background=lightgrey Just=r];
define vwgolf_new_pct/display "New Pct" style(column)=[tagattr='format:#,##0.0' just=R] style(header)=[background=lightgrey Just=r];

define vwgolf_existing_n/display "Existing N" style(column)=[tagattr='format:#,##0' just=R] style(header)=[background=lightgrey Just=r];
define vwgolf_existing_pct/display "Existing Pct" style(column)=[tagattr='format:#,##0.0' just=R] style(header)=[background=lightgrey Just=r];

define vwgolf_all_n/display "All N" style(column)=[tagattr='format:#,##0' just=R] style(header)=[background=lightgrey Just=r];
define vwgolf_all_pct/display "All Pct" style(column)=[tagattr='format:#,##0.0' just=R] style(header)=[background=lightgrey Just=r];

define vwgolf_new_switchers_n/display "New N" style(column)=[tagattr='format:#,##0' just=R] style(header)=[background=lightgrey Just=r];
define vwgolf_new_switchers_pct/display "New Pct" style(column)=[tagattr='format:#,##0.0' just=R] style(header)=[background=lightgrey Just=r];

define vwgolf_existing_switchers_n/display "Existing N" style(column)=[tagattr='format:#,##0' just=R] style(header)=[background=lightgrey Just=r];
define vwgolf_existing_switchers_pct/display "Existing Pct" style(column)=[tagattr='format:#,##0.0' just=R] style(header)=[background=lightgrey Just=r];

define vwgolf_all_switchers_n/display "All N" style(column)=[tagattr='format:#,##0' just=R] style(header)=[background=lightgrey Just=r];
define vwgolf_all_switchers_pct/display "All Pct" style(column)=[tagattr='format:#,##0.0' just=R] style(header)=[background=lightgrey Just=r];

run;

ods tagsets.excelxp close;
ods listing;
 | 
Pages: 1
Prev: Promote Business by Quality Advertising!
Next: User written macro or SAS supplied macro ??