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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.