BookmarkSubscribeRSS Feed
doylejm
Fluorite | Level 6

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;

3 REPLIES 3
Reeza
Super User

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...

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1650 views
  • 1 like
  • 4 in conversation