Dear experts,
given the following data:
data test;
input segment: $12. jan: 10. feb: 10. mar: 10.;
datalines;
A 150 200 155
B 170 150 200
;
and given the following output:
proc sql; create table test2 as select
segment, sum(feb) as sum_feb
from test group by 1 ;quit;
I would like to get the same using the following reference:
%let var=feb;
proc sql; create table test3 as select
segment, sum('&var') as sum_&var
from test group by 1 ;quit;
but as you can see it does not work. Any ideas? Thanks, SH.
variable names in a SQL select must not be enclosed in quotes. Text in quotes is considered a string literal.
Therefore do this:
data test;
input segment: $12. jan: 10. feb: 10. mar: 10.;
datalines;
A 150 200 155
B 170 150 200
;
run;
%let var=feb;
proc sql;
create table test3 as select
segment, sum(&var) as sum_&var
from test group by 1;
quit;
proc print noobs;
run;
getting this result:
segment sum_feb A 200 B 150
Log:
16 data test; 17 input segment: $12. jan: 10. feb: 10. mar: 10.; 18 datalines; NOTE: The data set WORK.TEST has 2 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 21 ; 22 run; 23 24 %let var=feb; 25 26 proc sql; 27 create table test3 as select 28 segment, sum(&var) as sum_&var 29 from test group by 1; NOTE: Table WORK.TEST3 created, with 2 rows and 2 columns. 30 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.00 seconds 31 32 proc print noobs; 33 run; NOTE: There were 2 observations read from the data set WORK.TEST3. NOTE: The PROCEDURE PRINT printed page 1. NOTE: PROCEDURE PRINT used (Total process time): real time 0.04 seconds cpu time 0.01 seconds
Dear experts,
givend the following fata:
data test;
input segment: $12. jan: 10. feb: 10. mar: 10.;
datalines;
A 150 200 155
B 170 150 200
;
and given the following output:
proc sql; create table test2 as select
segment, sum(feb) as sum_feb
from test group by 1 ;quit;
I would like to get the same using the following reference:
%let var=feb;
proc sql; create table test3 as select
segment, sum('&var') as sum_&var
from test group by 1 ;quit;
but as you can see it does not work. Any ideas? Thanks, SH.
proc sql;
create table test2 as select
segment, sum(feb) as sum_feb into:seg,
from test group by segment ; /* 1 means segment by now it is more readable */
quit;
then you can use &seg to get the sum of FEB per SEGment;
I haven't check it, so better run and check.
variable names in a SQL select must not be enclosed in quotes. Text in quotes is considered a string literal.
Therefore do this:
data test;
input segment: $12. jan: 10. feb: 10. mar: 10.;
datalines;
A 150 200 155
B 170 150 200
;
run;
%let var=feb;
proc sql;
create table test3 as select
segment, sum(&var) as sum_&var
from test group by 1;
quit;
proc print noobs;
run;
getting this result:
segment sum_feb A 200 B 150
Log:
16 data test; 17 input segment: $12. jan: 10. feb: 10. mar: 10.; 18 datalines; NOTE: The data set WORK.TEST has 2 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 21 ; 22 run; 23 24 %let var=feb; 25 26 proc sql; 27 create table test3 as select 28 segment, sum(&var) as sum_&var 29 from test group by 1; NOTE: Table WORK.TEST3 created, with 2 rows and 2 columns. 30 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.00 seconds 31 32 proc print noobs; 33 run; NOTE: There were 2 observations read from the data set WORK.TEST3. NOTE: The PROCEDURE PRINT printed page 1. NOTE: PROCEDURE PRINT used (Total process time): real time 0.04 seconds cpu time 0.01 seconds
Literally replace every word of FEB with &var
If in the middle of text use a period at the end to tell SAS it's the end of the macro variable.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.