topic Re: Using two macro variables with multiple & in sas macro in SAS Programming

Using two macro variables with multiple & in sas macro

Sejin — Fri, 23 Feb 2018 07:21:24 GMT

Hello.

I am having difficulty in quoting macro variables in my macro code.

Here is a brief example of my problem.

data _null;
set data1;
call symputx('fund'||left(_n_),crsp_portno);
run;

data _null;
set data2;
call symputx('time'||left(_n_),date);
run;

%macro name;

%local i;
%local j;

%do i=1 %to 2;
%do j=1 %to 2;

proc sql;
create table fund. f&&fund&i&&time&j._raw as
select * from somewhere
where crsp_portno=&&fund&i
quit;

%end;

%mend;

Espeically, I am having trouble with using the expression

f&&fund&i&&time&j._raw

I was expecting this code plays like

f&&fund&i&&time&j._raw

f&fund1&time1._raw

f100000120120331_raw

this way.

(Those values that were assigned to &fundi and &timej are all numeric with 7 or 8 digits)

However, it seems that I have poor understanding in using multiple &.

Could anyone give an idea for this?

Thanks for reading my thread.

Re: Using two macro variables with multiple & in sas macro

Patrick — Fri, 23 Feb 2018 09:07:56 GMT

@Sejin It's highly likely not a good design decision to create that many SAS tables. Not sure what you're trying to achieve but an index or something in this direction would eventually be a better approach.

But to answer your question: Each iteration for resolving the macro variable will consume one dot. So for your result to end-up with what you're after, you need at least two such dots (for the two ampersand to get resolved).

f&&fund&i&&time&j.._raw

For the call symputx() bit code as below would be cleaner:

call symputx(cats('fund',put(_n_,f5.)),crsp_portno);

Re: Using two macro variables with multiple & in sas macro

gamotte — Fri, 23 Feb 2018 09:05:53 GMT

Hello,

Please provide some example datasets in the form of data steps that can help us reproduce the problem

and tell us exactly he results you obtain. What is the log saying ?

If you have macrovariables foo_1, foo_2, ..., foo_n and a counter &i., the way to resolve them is &&foo_&i..

At first glance, your program seems correct except that there is a space after "fund." in your create table instruction.

Do you really need to create separate tables for each fund, time ? You can probably achieve your goal using

by group processing.