topic Re: Sas macro loop in SAS Programming

Sas macro loop

Kiteulf — Fri, 09 Nov 2018 19:48:21 GMT

I am getting an error due to the fact that the

one variable is not working &ID&nID

what am I doing wrong?

%put &ID1;
810034882
33 %put &ID2;
810182482
34 %put &ID3;
810724412
35 %put &ID1;
810034882
36 %put &nID;
3
37
38 %macro loop;
39
40 %do i= 1 %to &nID;
41
42
43 Proc sql;
44 create table bs_company_&ID&NID as
45 select distinct &ID&NID as orgnbr
46 ,t0.c
47 ,t0.c1
48 ,t0.c2
49
50 , t1.x
51 ,t1.xxx

52 ,t1.xxxx
53 ,t0.xxx1
54 ,t0.xxx2
55 ,t0.xxx3
56 ,t0.xxx4
57 ,t0.xxx5
58
59 from bs_pre_company as t0
60 left join s18_19.startfile2 as t1
61 on &ID&NID=t1.orgnbr
62
63
64
65
66
67 ;
68 quit;
69
70
71 %END;
72 %MEND LOOP;
73 %LOOP;

WARNING: Apparent symbolic reference ID not resolved.
NOTE: Line generated by the invoked macro "LOOP".
73 &ID&NID as select distinct
_
22
200
WARNING: Apparent symbolic reference ID not resolved.
ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: Line generated by the invoked macro "LOOP".
73 &ID&NID as organisationregistrationnbr
_
22
73 ! ,t0.company_customer_id ,t0.SourceSystId as Company_sourcesystid ,t0.contractnbr as company_contractnbr
73 ! ,input(put
WARNING: Apparent symbolic reference ID not resolved.
NOTE: Line generated by the invoked macro "LOOP".
73 &ID&NID=t1.organisationregistrationnbr where

22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

Re: Sas macro loop

Reeza — Fri, 09 Nov 2018 19:54:03 GMT

Try &ID.&NID. -> you need the periods to tell SAS where the macro variable ends.

Re: Sas macro loop

novinosrin — Fri, 09 Nov 2018 20:02:33 GMT

First off,

are you expecting -> select distinct &ID&NID to resolve in a column name that is all numbers?

Re: Sas macro loop

ballardw — Fri, 09 Nov 2018 20:35:13 GMT

To help us understand your log post it into a code box opened using the forum's {I} icon to preserve error message formats.

Second to help yourself debug macro errors use OPTIONS MPRINT; to show the entire text generated by your macro code.

Re: Sas macro loop

Tom — Fri, 09 Nov 2018 22:53:06 GMT

I think you are trying to get the value of the macro variables ID1, ID2, etc. Instead what you asked for is the value of the macro variable ID, which SAS is saying doesn't exist.

You need to use multiple &'s. When the macro processor sees two & it changes it into one a then re-processes the string to see if there are more macro references.

Try this

%let id1=SAM;
%let i=1;
%put ID&i = &&id&i ;

or this loop.

%do i= 1 %to &nID;
  %put ID&i = &&id&i ;
%end;

Re: Sas macro loop

Astounding — Fri, 09 Nov 2018 23:39:13 GMT

I think @Tom has identified the right issue here. To put it in the context of your program, this section tries to find &ID (which does not exist):

select distinct &ID&NID as orgnbr

Instead, use:

select distinct &&ID&NID as orgnbr

Re: Sas macro loop

Tom — Fri, 09 Nov 2018 23:42:10 GMT

Probably wants to use &I instead of &NID so that each time through the loop is different instead of repeating the same code &NID times.