BookmarkSubscribeRSS Feed
chandan_mishra
Obsidian | Level 7

If I have a dataset like this

 

MetricProdR_1R_2R_3R_4
NA1245
RA23345
NB21333
RB1234

 

I want to group by Metric and Prod.

Proc sql;
create table want as
select metric, prod, sum(r_1) as r_1, sum(r_2) as r_2, sum(r_3) as r_3, sum(r_4) as r_4
from have 
group by metric, prod;
quit;

 

Here since it has only 4 variables to sum so I can write from R_1 to R_4. What if I had 100 variables to sum? What is the efficient way of doing this?

 

Thanks

Chandan Mishra 

3 REPLIES 3
Kurt_Bremser
Super User

You can use a wildcard variable name in proc summary:

proc summary data=have sum;
by metric prod;
var r_:;
run;

If you absolutely want to do it in SQL, you can use a macro loop to generate the summations.

PGStats
Opal | Level 21

SAS/SQL doesn't support variable lists the way the data step and SAS procs do. One alternative is to do the SQL summations on a transposed version of your dataset.

PG
s_lassen
Meteorite | Level 14

The pure SQL way is to use dictionary.tables:

proc sql noprint; /* noprint because we do not want to see the SELECT INTO results as a table */
select catx(' ','sum(',name,') as', name) into :sums separated by ','
  from dictionary columns
  where libname='WORK' 
    and memname='HAVE'
    and upcase(name) like 'R%'
   ;
create table want as select
  metric,
  prod,
  &sums
from have
group by metric,prod
;
quit;

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
  • 2827 views
  • 4 likes
  • 4 in conversation