DATA Step, Macro, Functions and more

how to refer to a variable using %let

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

how to refer to a variable using %let

[ Edited ]

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.


Accepted Solutions
Solution
‎08-24-2016 09:58 AM
Super User
Posts: 6,963

Re: how to refer to a variable using %let

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Frequent Contributor
Posts: 127

how to refer to a variable using let

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.

Trusted Advisor
Posts: 1,400

Re: how to refer to a variable using %let

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.

Solution
‎08-24-2016 09:58 AM
Super User
Posts: 6,963

Re: how to refer to a variable using %let

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 17,905

Re: how to refer to a variable using %let

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 260 views
  • 2 likes
  • 4 in conversation