BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sir_Highbury
Quartz | Level 8

Dear experts,

 

let's assume the following variables: shop_ID, bolean_variable, revenue201601, revenue201602, revenue201603, revenue201603

for a total of 10 observations.

 

how can set a proc sql as it follows: proc sql; create table abc as select   shop_ID, bolean_variable, sum (revenue:) from xyz group by  shop_ID, bolean_variable ;quit;

 

How can I specify to do it for some variables without listing them one by one?

Thanki in advance and BRs, SH

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Are you looking to sum within year the 4 revenue variables and then get a single aggregate total or do you want 4 aggregate sums?

 

I think if your main criteria is the use of variable lists you may want to look to something like Proc summary or means instead of sql.

 

proc summary data= xyz nway;

   class shop_id bolean_variable;

   var revenue: ;

   output out=abc (drop= _:) sum=;

run;

View solution in original post

4 REPLIES 4
jklaverstijn
Rhodochrosite | Level 12

You cannot specify variable ranges or wildcards in the SELECT clause as you would have in a datastep. But you can use KEEP on the table:

 

proc sql;
   select *
   from have(keep=shop_id revenue2016:);
quit;

You could also use shortcut notation like VAR1-VAR8, VAR1--VAR8, _NUMERIC_ etc. But always on the KEEP, never on the SELECT.

 

Regards, Jan.

ballardw
Super User

Are you looking to sum within year the 4 revenue variables and then get a single aggregate total or do you want 4 aggregate sums?

 

I think if your main criteria is the use of variable lists you may want to look to something like Proc summary or means instead of sql.

 

proc summary data= xyz nway;

   class shop_id bolean_variable;

   var revenue: ;

   output out=abc (drop= _:) sum=;

run;

sh0e
Obsidian | Level 7

This is DEFINITELY a task for PROC SUMMARY as @ballardw notes.

Astounding
PROC Star

As was noted, there are simpler solutions than using SQL.  But if you are determined to use a SQL solution, macro language can help.  Your intention makes a difference.  Which of these would be correct within a SELECT statement?

 

sum(revenue1) as revenue1, sum(revenue2) as revenue2, sum(revenue3) as revenue3

 

vs.

 

sum(revenue1, revenue2, revenue3) as tot_revenue

 

If PROC SUMMARY works for what you need, that would be simpler.  No need to mess with macro language unless necessary.

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
  • 4 replies
  • 5344 views
  • 1 like
  • 5 in conversation