Not at all sure what would cause this to create "300,000 different tables";
Take a look at the following. If I understand correctly you have a data set with the information all ready transposed so could start directly with the first proc tabulate step.
data work.have;
informat Name $10. Loc1 Loc2 Loc3 Loc4 Loc5 $1.;
input Name Loc1 Loc2 Loc3 Loc4 Loc5;
datalines;
Sarah A B A A A
James B B C D .
Albert D E F . .
Rosie E E E A A
William B B B B .
Ashley D D . . .
;
run;
Proc transpose data=work.have out=work.trans (drop=_name_)
;
by notsorted name;
var loc: ;
run;
Proc tabulate data=work.trans out=work.tab;
class name;
class col1;
table name,
col1='Location' *(n rowpctn)
/ misstext=' '
;
run;
proc sort data=work.tab out=work.tabsort(drop= _type_ _page_ _table_);
by name descending N;
run;
data work.want;
set work.tabsort;
by name;
if first.name then order=1;
else order+1;
run;
proc report data=work.want;
columns name order,(col1 n pctn_10);
define name /group;
define order/'Visit order' across;
label col1='Location'
n = 'Visits'
pctn_10 = '% of visits'
;
format pctn_10 best4.;
run;
If you are doing lots of different variables then the first proc tabulate step output might need to be looked at carefully and the sort might want to consider the variables _type_ or _table_ as those indicate import bits of information and depend on the structure of the Proc tabulate table statements.
If you have more than one variable you are summarizing in a manner similar to the Location then you would have some choices of tabulate code such as
Proc tabulate data=work.trans out=work.tab;
class name;
class col1 other;
table name,
(col1='Location' other) *(n rowpctn)
/ misstext=' '
;
run;
Proc tabulate data=work.trans out=work.tab2;
class name;
class col1 other;
table name,
col1='Location' *(n rowpctn)
/ misstext=' '
;
table name,
other *(n rowpctn)
/ misstext=' '
;
run;
With one you would look at _type_ and the other _table_. You might also get differently named pct variables.
You would also have to consider if any of your class variables were missing as tabulate by default excludes those records from calculations. Which is another reason not to use the wide version of the data as you end up with LOTS of missing values.
... View more