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

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

doylejm — Sun, 05 Mar 2017 21:08:33 GMT

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;

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

Reeza — Sun, 05 Mar 2017 22:19:24 GMT

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#p0wphcpsfgx6o7n1sjtqzizp1n39.htm

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

art297 — Sun, 05 Mar 2017 22:14:39 GMT

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

Art, CEO, AnalystFinder.com

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

PGStats — Sun, 05 Mar 2017 23:24:39 GMT

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;