topic Re: empty list stored in a macro code in SAS Procedures

empty list stored in a macro code

tampham92 — Thu, 17 May 2018 19:48:23 GMT

Hi,

I ran this code below to store a list of values into a macro variable.

select distinct AccountTypeCode1 into :Merrill separated by ','
from ACCOUNT_CODE_MASTER
where GLNumber1 = '208143' AND TaxPayerIDRAFT = '135674085'
;

Then I used that list in a data step as below

DATA TEST;
SET have;

TEMP = input(Code, best3.);
format TEMP z3.;

if temp in (&Merill.) then prod = "Merill"

else prod = .;
RUN;

but the log gave me error saying that

WARNING: Apparent symbolic reference CONS_IA_BANKSAFE not resolved.

So I went back to the first piece of code to detect the issue and I found out that no rows are selected for that &Merill. macro.

Basically, I wanna keep the first piece of code whether I determine the list, but then in the DATA step, how can I go around an empty macro? I have several macro variables within that DATA STEP, but when SAS detects an empty macro, it gave me error and does not process the rest of the code.

Re: empty list stored in a macro code

Astounding — Thu, 17 May 2018 21:30:55 GMT

You can initialize the macro variable yourself. For example, before running PROC SQL:

%let Merrill = 'You will never find this one!';

Then SQL either replaces the value or (if no matches are found) leaves it alone.

Other items to note ...

Your error message does not refer to &MERRILL. It refers to &CONS_IA_BANKSAFE. Presumably, you are posting the right section of code here.

ELSE PROD = .;

That's a missing value for a numeric variable. That's not compatible with PROD being a character variable that might be set to "Merrill".

********************** EDITED:

Sorry, didn't notice that you were using a numeric variable TEMP. So my answer has to be amended to assign a value that would never actually be found:

%let Merrill = -5;

That statement should be inserted into the program just before PROC SQL.

That will handle the original problem of a macro variable not being found. There are still other issues, but that change will get you through the first issue.

Re: empty list stored in a macro code

PaigeMiller — Thu, 17 May 2018 19:57:24 GMT

@tampham92 wrote:

how can I go around an empty macro? I have several macro variables within that DATA STEP, but when SAS detects an empty macro, it gave me error and does not process the rest of the code.

After PROC SQL

%if &merrill ^= %then %do;

data test;
 ....
run;

%end;

Re: empty list stored in a macro code

SuryaKiran — Thu, 17 May 2018 20:27:53 GMT

Try this:

if temp in (&Merill.) and not missing("&Merill.") then prod = "Merill"

Re: empty list stored in a macro code

tampham92 — Thu, 17 May 2018 20:23:54 GMT

where do I insert that %if code?

My code is below

proc sql;
select distinct AccountTypeCode1 into :Merrill separated by ','
from ACCOUNT_CODE_MASTER
where GLNumber1 = '208143' AND TaxPayerIDRAFT = 'xxxxx'
;
run;

DATA TEST;
SET have;

TEMP = input(AccountTypeCode, best3.);
format TEMP z3.;

if temp IN (&Merrill.) then QRMSA = "Merrill";
run;

Re: empty list stored in a macro code

tampham92 — Thu, 17 May 2018 20:24:24 GMT

it did not work. can you please be more specific on what I should do here?

Re: empty list stored in a macro code

SuryaKiran — Thu, 17 May 2018 20:38:47 GMT

If the query returns empty result then no macro will be created. Then use %symexist to check whether you macro exists. It return 1 if macro exists and 0 if not.

if temp in (&Merill.) and %symexist(Merill)=1 then prod = "Merill"

Re: empty list stored in a macro code

tampham92 — Thu, 17 May 2018 21:03:55 GMT

I did what you suggest and the log still gave me errors

25 GOPTIONS ACCESSIBLE;
26 DATA TEST;
27 SET HAVE;

29 format QRMSA $30.;

37 TEMP = input(AccountTypeCode, best3.);
38 format TEMP z3.;

42 if temp in
42 ! (&Merill.) and %symexist(&Merill.) = 1 then QRMSA = "Merill";
_
22
WARNING: Apparent symbolic reference MERILL not resolved.
WARNING: Apparent symbolic reference MERILL not resolved.

ERROR: Macro variable name &MERILL. must start with a letter or underscore.
42 &Merill.) and %symexist(&Merill.) = 1 then QRMSA = "Merill";
_
200
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, iterator, (.

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

Re: empty list stored in a macro code

SuryaKiran — Thu, 17 May 2018 21:16:13 GMT

if %symexist(Merill) =1 then do;
			if  temp in (&Merill.) then QRMSA = "Merill";
			end;

NOTE: Macro variable will not be created from an empty result table and previous macro values still exist.

Re: empty list stored in a macro code

Cynthia_sas — Thu, 17 May 2018 21:36:43 GMT

Hi:

While all of these suggestions are good, my idea is to go further back in the debugging process. I assume that this step is a PROC SQL step?

proc sql;
select distinct AccountTypeCode1 into :Merrill separated by ','
from ACCOUNT_CODE_MASTER
where GLNumber1 = '208143' AND TaxPayerIDRAFT = '135674085'
;
quit;
  
%put what is in the Merrill macro variable &=Merrill;

I suggest that BEFORE you use &Merrill anyplace you do some simple debugging AFTER the PROC SQL step. For example, use a %PUT to make sure that &Merrill has the AccountTypeCode1 that you expect.

Or, try a proc print of Account_Code_Master and test your WHERE statement:

proc print data=ACCOUNT_CODE_MASTER;
var AccountTypeCode1 GLNumber1 TaxPayerIDRAFT;
where GLNumber1 = '208143' AND TaxPayerIDRAFT = '135674085'
run;

Or use a PROC FREQ to see whether the variable values you've got in the WHERE are even present in Account_Code_Master:

proc freq data=ACCOUNT_CODE_MASTER;
  tables GLNumber1 TaxPayerIDRAFT;
run;

Or even something simple like using a PROC CONTENTS to be sure that the syntax of your WHERE is correct (in other words that both variables are character):


proc contents data=ACCOUNT_CODE_MASTER;
run;

Because if GLNumber1 and TaxPayerIDRAFT are actually numeric variables and not character variables, then the syntax of your WHERE is incorrect.

You did not say whether you had done these basic debugging steps first. That's where I would start. Perhaps you've done these things and you know that both variables are character and exist in the Account_Code_Master data. However, it never hurts to double check these things.

Cynthia

Re: empty list stored in a macro code

ballardw — Thu, 17 May 2018 22:47:28 GMT

Note that if your proc sql code to generate the macro list actually looks like this

proc sql noprint;
   select distinct AccountTypeCode1 into :Merrill separated by ','
   from ACCOUNT_CODE_MASTER
   where GLNumber1 = '208143' AND TaxPayerIDRAFT = '135674085'
   ;
quit;

An automatic macro variable SQLOBS was set with how many items were returned.

I would save it to specific macro variable as it may get reset before you need it with something like:

%let MerrillCount = &sqlobs;

Then in your data step you could actually test and use

%if &Merrillcount > 0 %then %do;

%end;

%else %do;

%end;

Re: empty list stored in a macro code

tampham92 — Fri, 18 May 2018 13:21:41 GMT

it still gave me error at this step

Is there I can turn an empty macro in a "."