BookmarkSubscribeRSS Feed
DanD
Calcite | Level 5


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.

5 REPLIES 5
ballardw
Super User

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.

dan999
Fluorite | Level 6

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.

DanD
Calcite | Level 5

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

Tom
Super User Tom
Super User

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;

DanD
Calcite | Level 5

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1098 views
  • 0 likes
  • 4 in conversation