topic how to refer to a variable using let in SAS Programming

how to refer to a variable using %let

Sir_Highbury — Wed, 24 Aug 2016 13:26:42 GMT

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.

how to refer to a variable using let

Sir_Highbury — Wed, 24 Aug 2016 13:24:53 GMT

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.

Re: how to refer to a variable using %let

Shmuel — Wed, 24 Aug 2016 13:32:23 GMT

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.

Re: how to refer to a variable using %let

Kurt_Bremser — Wed, 24 Aug 2016 13:39:42 GMT

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

Re: how to refer to a variable using %let

Reeza — Wed, 24 Aug 2016 13:40:38 GMT

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.