BookmarkSubscribeRSS Feed
tampham92
Obsidian | Level 7

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. 

11 REPLIES 11
Astounding
PROC Star

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.

PaigeMiller
Diamond | Level 26

@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
tampham92
Obsidian | Level 7

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;

SuryaKiran
Meteorite | Level 14

Try this:

 

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

Thanks,
Suryakiran
tampham92
Obsidian | Level 7

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

SuryaKiran
Meteorite | Level 14

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
tampham92
Obsidian | Level 7

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.

SuryaKiran
Meteorite | Level 14

 

 

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
Cynthia_sas
SAS Super FREQ

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

 

 

tampham92
Obsidian | Level 7

it still gave me error at this step

error.PNG

 

 

 

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

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 2496 views
  • 0 likes
  • 6 in conversation