topic Re: creating new columns and rows with summary data using proc sql in New SAS User

creating new columns and rows with summary data using proc sql

Re: creating new columns and rows with summary data using proc sql

Re: creating new columns and rows with summary data using proc sql

ghbg — Fri, 18 Sep 2020 13:32:44 GMT

I have a dataset that looks like this:

(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 — Fri, 18 Sep 2020 14:04:48 GMT

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.

bought_hat

bought_gloves

wore_gloves

bought_scarf

wore_scarf

Cynthia_sas — Fri, 18 Sep 2020 16:28:43 GMT

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