DATA Step, Macro, Functions and more

How to combine rows of data using PROC SQL's sum funciton for a large number of variables

Reply
Occasional Contributor
Posts: 6

How to combine rows of data using PROC SQL's sum funciton for a large number of variables

I am trying to sum two rows of data for each year in a longitudinal data set.  The following code using PROC SQL works but I will have a much larger data set with  many variables that need to be summed.  In the code blow I have to give a sum command in SQL for each variable.  Wildcards in SQL?  Thank you. 

 

data have;
input id year pop mt;
datalines;
2 2010 95247.15 677297
2 2011 98970 527331
2 2012 100780 665843
2 2013 101575 613688
2 2014 102731 537205
4 2010 16884.8 6640444
4 2011 16250 6639846
4 2012 16180 6407018
4 2013 15937 6757319
4 2014 15961 5404051
;
Proc sql;
create table want as
select year, sum(pop) as sum_pop, sum(mt) as sum_mt
from have
group by year;
quit;

Super User
Posts: 17,829

Re: How to combine rows of data using PROC SQL's sum funciton for a large number of variables

[ Edited ]

There are no wildcards or shortcuts for variables in SQL. Use one of the summary procedures such as PROC MEANS, SUMMARY or UNIVARIATE. You can use the variable lists or shortcuts in the VAR statement, or if you exclude a VAR statement all numeric variables will be summarized.

 

proc means data=have nway noprint;
class year;
var pop mt;
output out=want sum= /autoname;
run;

 

EDIT: See the ways to specify a variable list here:

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p0wphcpsfgx6o7n1sjtq...

PROC Star
Posts: 7,363

Re: How to combine rows of data using PROC SQL's sum funciton for a large number of variables

Post a list that includes all of the variables that you'd like to select using wildcards.

 

Art, CEO, AnalystFinder.com

 

Respected Advisor
Posts: 4,649

Re: How to combine rows of data using PROC SQL's sum funciton for a large number of variables

If you absolutely want to do this with SQL, you will have to resort to macro programming:

 


proc sql noprint;
select catt("sum(", name, ") as", " sum_", name)
into :varlist separated by ", "
from dictionary.columns
where libname="WORK" and memname="HAVE" and upcase(name) not in ("ID","YEAR");
create table want as
select
    id, year, &varlist.
from have
group by id, year;
quit;
PG
Ask a Question
Discussion stats
  • 3 replies
  • 144 views
  • 1 like
  • 4 in conversation