Date:         Wed, 19 Sep 2007 11:23:50 -0400
Reply-To:     Lingqun Liu <lingqun@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Lingqun Liu <lingqun@GMAIL.COM>
Subject:      Re: How to avoid repeating running proc sql 121 times?
Comments: To: Duck-Hye Yang <dyang@chapinhall.org>
In-Reply-To:  <46EFE016.E137.00EA.3@chapinhall.org>
Content-Type: text/plain; charset=ISO-8859-1

You don't have to run it 121 times.  You can do it in one query.

proc sql;
select provid,childid,sum(dummy1) as sum_dummy1,sum(dummy2) as sum_dummy2, ...,
from table_name
group by provid,childid;
quit;


If you meant  to save the typing of 121 variable names then you may
try to write some lines to create the variable list (sum(dummy1) as
sum_dummy1,sum(dummy2) as sum_dummy2, ...,) and store it into a macro
variable first.  ( PROC content may be useful. )

proc sql;
select provid,childid, &variable_list
from table_name
group by provid,childid;
quit;

Best,

Lingqun

On 9/18/07, Duck-Hye Yang <dyang@chapinhall.org> wrote:
> Hi,
> I have providerID and childID. I'd like to sum dummy variable (its value has 1 or 0) by providerID and childID.
> Here is an example of the data:
> providerID  childID  dummy1
> 1                    3           1
> 1                    4           0
> 1                    3           1
> 2                    4           0
> 2                    3           1
>
> I could do it easily with proc sql.
> The problem is that I have 121 dummy variables!!!
>
> Duckhye
>