Help using Base SAS procedures

empty list stored in a macro code

Reply
Occasional Contributor
Posts: 17

empty list stored in a macro code

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. 

Super User
Posts: 6,759

Re: empty list stored in a macro code

[ Edited ]
Posted in reply to tampham92

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.

Respected Advisor
Posts: 2,997

Re: empty list stored in a macro code

Posted in reply to tampham92

@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;
--
Paige Miller
Occasional Contributor
Posts: 17

Re: empty list stored in a macro code

Posted in reply to PaigeMiller

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;

Valued Guide
Posts: 590

Re: empty list stored in a macro code

[ Edited ]
Posted in reply to tampham92

Try this:

 

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

Thanks,
Suryakiran
Occasional Contributor
Posts: 17

Re: empty list stored in a macro code

Posted in reply to SuryaKiran

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

Valued Guide
Posts: 590

Re: empty list stored in a macro code

[ Edited ]
Posted in reply to tampham92

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"

Thanks,
Suryakiran
Occasional Contributor
Posts: 17

Re: empty list stored in a macro code

[ Edited ]
Posted in reply to SuryaKiran

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.

Valued Guide
Posts: 590

Re: empty list stored in a macro code

[ Edited ]
Posted in reply to tampham92

 

 

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. 

Thanks,
Suryakiran
SAS Super FREQ
Posts: 9,366

Re: empty list stored in a macro code

[ Edited ]
Posted in reply to SuryaKiran

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

 

 

Occasional Contributor
Posts: 17

Re: empty list stored in a macro code

Posted in reply to SuryaKiran

it still gave me error at this step

error.PNG

 

 

 

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

Super User
Posts: 13,517

Re: empty list stored in a macro code

Posted in reply to tampham92

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;

<the code dependent on having some values in the Merril variable>

%end;

%else %do;

<the code you might like to see if the macro variable is empty>

<maybe something like: If _n_=1 the put WARNING: No variables in macro variable Merrill. ;>

 

%end;

Ask a Question
Discussion stats
  • 11 replies
  • 229 views
  • 0 likes
  • 6 in conversation