Help using Base SAS procedures

Creating a summary table from long list of variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Creating a summary table from long list of variables

I'm sure this is very basic, but I'm having a  mind block and would appreciate some help.

Suppose I had data like this which is a list of names, dates and fruit and veg consumption recorded by dummy variablese

NameDayBananaCarrotApple
Bob1000
Bob2100
Bob5110
Claire1001
Claire2001
Claire3101
Claire4101

And I wished to produce a summary table like this

NameDaysBananaCarrotApple
Bob30.670.330
Claire40.501

What would be the best way about it (and imagine I had 50 more types of fruit and veg consumption and don't want to type in all their names).

Many thanks, Chris


Accepted Solutions
Solution
‎04-14-2015 09:28 AM
Super User
Posts: 10,028

Re: Creating a summary table from long list of variables

Posted in reply to cb23_york

Are there some missing days or duplicated days for a name ?


data have;
input name $ day banana carrot apple;
cards;
Bob     1     0     0     0
Bob     2     1     0     0
Bob     5     1     1     0
Claire     1     0     0     1
Claire     2     0     0     1
Claire     3     1     0     1
Claire     4     1     0     1
;
run;
proc sql;
 select cat('sum(',strip(name),')/count(*) as ',strip(name)) into : list separated by ','
  from dictionary.columns
   where libname='WORK' and memname='HAVE' and upcase(name) not in ('NAME' 'DAY');
 
 create table want as
  select name,count(*) as days,&list
   from have
    group by name;
quit;

Xia Keshan

View solution in original post


All Replies
Super User
Super User
Posts: 7,955

Re: Creating a summary table from long list of variables

Posted in reply to cb23_york

What does the summary table mean?  Why does Bob have 3 days and 0.67 for instance?  If you don't want to type each one, then use arrays and numeric suffix variables:

data tmp;

     array fruit{3} 8.;

     do i=1 to 3;

          ...

     end;

run;

Occasional Contributor
Posts: 16

Re: Creating a summary table from long list of variables

To clarify, the table should contain each individuals name, a column counting the number of distinct days that we have an observation for that individual and the following columns should summarise the proportion of times that individual consumed that fruit or veg.  Bob has 3 data entries ( days 1,2 and 5) and consumed a banana on 66.6% of those days a carrot on 33.3% of those days and an apple on 0% of those days.  Hope that is clearer, thanks.

Solution
‎04-14-2015 09:28 AM
Super User
Posts: 10,028

Re: Creating a summary table from long list of variables

Posted in reply to cb23_york

Are there some missing days or duplicated days for a name ?


data have;
input name $ day banana carrot apple;
cards;
Bob     1     0     0     0
Bob     2     1     0     0
Bob     5     1     1     0
Claire     1     0     0     1
Claire     2     0     0     1
Claire     3     1     0     1
Claire     4     1     0     1
;
run;
proc sql;
 select cat('sum(',strip(name),')/count(*) as ',strip(name)) into : list separated by ','
  from dictionary.columns
   where libname='WORK' and memname='HAVE' and upcase(name) not in ('NAME' 'DAY');
 
 create table want as
  select name,count(*) as days,&list
   from have
    group by name;
quit;

Xia Keshan

Occasional Contributor
Posts: 16

Re: Creating a summary table from long list of variables

That's fantastic Xia, much appreciated.  In answer to your question there indeed some missing days, but never any duplication of days for a name.

Super User
Posts: 10,028

Re: Creating a summary table from long list of variables

Posted in reply to cb23_york

So you want count this missing day or not ?

If you don't want count missing day , then change it as

proc sql;

select cat('sum(',strip(name),')/count(day) as ',strip(name)) into : list separated by ','

  from dictionary.columns

   where libname='WORK' and memname='HAVE' and upcase(name) not in ('NAME' 'DAY');

create table want as

  select name,count(day) as days,&list

   from have

    group by name;

quit;

Xia Keshan

Occasional Contributor
Posts: 16

Re: Creating a summary table from long list of variables

Thanks Xia,

I do indeed not want to count the missing days, but the first code works fine as well.  In the example Bob has missing days 3 and 4, but the original code does fine as counting that he has 3 days of observed data.

Super User
Posts: 10,028

Re: Creating a summary table from long list of variables

Posted in reply to cb23_york

Nope. I mean

Bob     .     0     0     0

Bob     .     1     0     0

Bob     5     1     1     0

Occasional Contributor
Posts: 16

Re: Creating a summary table from long list of variables

Ok that's clear.  No, there are no observations with missing data on the day.  But thank you for the amended code which would work under such circumstances.

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 359 views
  • 0 likes
  • 3 in conversation