BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
alparle
Calcite | Level 5

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.);

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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?

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Super User

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?

 

The Boston Area SAS Users Group (BASUG) is hosting our in person SAS Blowout on Oct 18!
This full-day event in Cambridge, Mass features four presenters from SAS, presenting on a range of SAS 9 programming topics. Pre-registration by Oct 15 is required.
Full details and registration info at https://www.basug.org/events.
Tom
Super User Tom
Super User

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
Jade | Level 19

What are you trying to achieve in the end?

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1363 views
  • 4 likes
  • 5 in conversation