creating new columns and rows with summary data using proc sql

[ghbg]

I have a dataset that looks like this:

personID	bought_hat	wore_hat	bought_gloves	wore_gloves	bought_scarf	wore_scarf
1	1	0	1	1	0	0
2	1	1	1	0	1	0
3	0	0	1	1	1	0

(1=yes, 0=no)

and I want to use proc sql to summarise the total bought/wore of each item of clothing as well as a total for all items of clothing like this:

item	total bought	total worn
hat	2	1
gloves	3	2
scarf	2	0
all items	7	3

any ideas?

i know i can use proc freq, but in reality i have far more than two columns for each item of clothing, and far more than three items of clothing, and far more than three people.

thanks

ghbg

[Kurt_Bremser]

First, make your data intelligent by transposing:

proc transpose data=wide out=long (rename=(_name_=item_use) where=(col1 = 1));
by personid;
var bought_hat--wore_scarf;
run;

Then extract the use type and the item from item_use:

data final;
set long;
item = scan(item_use,2);
bought = 0;
wore = 0;
select (scan(item_use,1));
  when ('bought') bought = 1; 
  when ('wore') wore = 1;
end;
run;

and you can use SAS procedures like SUMMARY, FREQ or SQL to get your sums.

[Cynthia_sas]

Hi:

  In the interest of providing alternatives, again, if you transpose your data but modify it slightly differently, then you can use either PROC REPORT or PROC TABULATE to create these results:

Here's the code:

data fakedata;
infile datalines;
input personID bought_hat wore_hat bought_gloves wore_gloves bought_scarf wore_scarf;
return;
datalines;
1 1	0 1	1 0 0
2 1	1 1 0 1 0
3 0	0 1	1 1 0
;
run;

proc transpose 
   data=fakedata 
   out=tr_long ;
by personid;
var bought_hat--wore_scarf;
run;

data final;
  length Item $15 Status $8;
  set tr_long;
  Item = propcase(scan(_name_,2,'_'));
  Status = propcase(scan(_name_,1,'_'));
run;

proc report data=final;
title 'Summary with PROC REPORT';
  column item status,col1;
  define item / group style(column)=Header;
  define status/ across ' ';
  define col1 / 'Total';
  rbreak after / summarize style=Header;
  compute after;
    item='Total';
  endcomp;
run;
title;
  
proc tabulate data=final f=3.0;
title 'Summary with PROC TABULATE';
  class item status;
  var col1;
  table item=' ' all='Total'*{style=Header},
        status=' '*col1='Total'*sum=' '/
        box={label='Item' style={vjust=b}};
run;

Cynthia