how can i define a macro variable by using another macro variable

[alparle]

Hi, i would like to define many macro variables by using "reference table (datahave) namely 

&nblock1 , &nblock_2 . could anyone pls solve or give an idea ? thank you in advance. Best Regards.

data datahave;
input seq_num $ cblock $;
datalines;
1 c1,c2
2 c3,c4
; run;

options symbolgen;

%let start = 1 ;
%let end = 2 ;

%macro macvar (start,end);
%do seq_num = &start. %to &end. ;

%let nblock_&seq_num. =
(select nblock from work.datahave where seq_num = &sec_num.);

%end;
%mend ;
%macvar(start=&start , end=&end)
;

note: 

if i define macro one by one ( i need more than 30) it works.

%let nblock_1 =  (select nblock from work.datahave where seq_num = &sec_num.);

[ballardw]

I don't know where the typo(s) may be. Your data set shows variable seq_num and cblock. You have this "(select nblock from work.datahave" which from the shown data does NOT have any "nblock".

Assuming since you used "nblock" more time that you mean nblock instead of Cblock as the macro variable name:

data datahave;
 input seq_num $ cblock $;
datalines;
1 c1,c2
2 c3,c4
; run;

data _null_;
   set datahave;
   call symputx(catx('_','nblock',seq_num),cblock);
run;

No macro needed to create sequentially numbered macro variables.

Don't even need the seq_num variable unless you need values other than actual sequence. This uses the data step increment counter to number macro variables sequentially in order of appearance in a data set.

data _null_;
   set datahave;
    call symputx(catx('_','nblock',_n_),cblock);
run;

[Quentin]

I think you have a typo sec_num where you intend seq_num.

If I change that, the code "works" (i.e. runs without errors) but is probably not doing what you intend. The macro variables NBlock_1 and NBlock_2 will have the text for a SQL query.

data datahave;
input seq_num $ cblock $;
datalines;
1 c1,c2
2 c3,c4
;
run;

%let start = 1 ;
%let end = 2 ;

%macro macvar (start,end);
%do seq_num = &start. %to &end. ;
  %let nblock_&seq_num. =
  (select nblock from work.datahave where seq_num = &seq_num.);
%end;

%put _local_ ;
%mend ;

%macvar(start=&start , end=&end)

Log shows:

255  %macvar(start=&start , end=&end)
MACVAR END 2
MACVAR NBLOCK_1 (select nblock from work.datahave where seq_num = 1)
MACVAR NBLOCK_2 (select nblock from work.datahave where seq_num = 2)
MACVAR SEQ_NUM 3
MACVAR START 1

Can you back up and describe the big picture? Are you trying to make two macro variables NBlock_1 and NBlock_2 from datahave, because it has two records?  And what do you want the value of those macro variables to be?

[Tom · Accepted solution]

Do you want NBLOCK_1 to have the string

(select nblock from work.datahave where seq_num = 1)

Or do you want NBLOCK_1 to have the value of NBLOCK from the dataset DATAHAVE?

proc sql noprint;
select nblock
  into :NBLOCK_&seq_num
  from datahave
  where seq_num = &seq_num
;
quit;

If the later will there just be one observation with SEQ_NUM=1 ?  Or will there be multiple?  If multiple do you want to concatenate the values so you store them into one macro variable?  If so then use the SEPARATED BY option.  For example if you wanted them separated by a space then use

  into :NBLOCK_&seq_num separated by ' '

Do you need the macro variables to exist after the macro finishes running?  Then make sure to add a %GLOBAL statement.  Or better only add the %GLOBAL statement when needed.

%if not %symexist(NBLOCK_&seq_num) then %global NBLOCK_&seq_num;
select nblock
  into :NBLOCK_&seq_num
  from datahave
  where seq_num = &seq_num
;

But then why not just use a DATA step?

data _null_;
  set datahave;
  where seq_num between &start and &end ;
  call symputx(cats('NBLOCK_',seq_num),nblock);
run;

If you need them forced into the GLOBAL macro scope then add the optional third argument.

  call symputx(cats('NBLOCK_',seq_num),nblock,'g');

The real questions though are What are you going to DO with a whole series of macro variables? And Why can't you just use the dataset instead?

[andreas_lds]

What are you trying to achieve in the end?