topic Re: help with a macro in SAS Programming

help with a macro

DanD — Tue, 08 Oct 2013 22:11:19 GMT

Someone gave me some code that I can't get to work. The object of the code is that if there are no results in my final query, I want to put a message in the output file to let the recipient of the report know that there were no results.

I first created some data and ran the code to see if it would run through but I'm getting "error 180-322 Statement is invalid or it is out of proper order" at the line %mend NoRecsFound;

Any ideas what the problem is?

On another subject is there a way to paste code into the message? I see insert video, link and image but not text.

Thanks,

Dan D.

Re: help with a macro

ballardw — Tue, 08 Oct 2013 22:50:32 GMT

I couldn't duplicate your result with SAS 9.2.3. However my editor showed an unidentified non-displayed character in the line before the macro definition. I've had unexpected results when that happens so if that character is in your code it may be worth deleteing.

You may also be having issues with:

1) the first SQL should end in quit instead of run (minor)

2) the field fst_name is undefined, at least in the example data and generates an error when the macro executes with a=0. When I add the character variable fst_name to the data it behaves as I think you want.

There are other ways, but generally to paste text into this forum it works best to come from a pure text editor. The old SAS Progam editor (not the enhanced) will work.

Re: help with a macro

dan999 — Wed, 09 Oct 2013 02:09:24 GMT

I think you're probably right about the non-displayed character. I had trouble with the first proc sql until I finally rewrote it and it worked. I'll retype it all tomorrow and test it. Thanks for the help.

Re: help with a macro

Tom — Wed, 09 Oct 2013 02:12:00 GMT

The strange character in the program you attached is ASCII code A0 hex ('A0'X in SAS literals) or 160 decimal.

Some editors stick those in because they look like spaces in most fonts and make it easier to pad lines without text formatted collapsing the multiple spaces into one.

Also in the INTO clause in your PROC SQL you can add SEPARATED BY clause and SAS will trim the leading spaces from the count.

You can paste into the editor just like you paste into any other program. Personally I use Ctrl-V to paste.

data final;

infile datalines;

input country $ barrels;

datalines;

us 96400

uk 96401

ru 96402

;

proc sql noprint;

select count(*)

into :cnt separated by ' '

from final

;

quit ;

%macro NoRecsFound;

%if &cnt = 0 %then %do;

proc sql;

insert into work.final

set fst_name = "No Results for this week";

quit;

%end;

%mend NoRecsFound;

%NoRecsFound;

Re: help with a macro

DanD — Wed, 09 Oct 2013 12:28:33 GMT

I discovered that if I try to copy and paste from SAS EG 5.1 that I'm not able to paste into here. But if I paste into Notepad and then copy and paste from Notepad into here, it works. Weird.

Thanks for all of the help.

Re: help with a macro

DanD — Wed, 09 Oct 2013 12:38:16 GMT

I retyped the macro part and it worked. Thanks again.