BookmarkSubscribeRSS Feed
Anna_nag
Obsidian | Level 7

Hi All,

I have a data set which  with column MAX that has value 1,2,4,6,8 and . (missing).

I want to display a message in log saying "missing value found". So I am approaching it like this but it is not working for me. Please suggest.

%macro try/minoperator;

proc sql noprint;

     select distinct max into: max_val separated by ', '

     from bin_table;

quit;

%if &max_val in . %then %do;

     %put "ERROR:Missing value found";

%end;

%mend try;

9 REPLIES 9
stat_sas
Ammonite | Level 13

data have;

input max;

datalines;

1

2

.

4

6

8

;

data _null_;

set have;

if max=. then put "ERROR:Missing value found";

run;

ballardw
Super User

Depending on you need and options for your log window you may get slightly different appearance using either "WARNING: " or "NOTE: " to start that message.

JerryLeBreton
Pyrite | Level 9

There are easier ways to achieve your objective but the code doesn't work because:

1. The condition should be     %if . in &max_val

2. The delimiter should just be a space (otherwise if you want use a comma use   %macro try/minoperator mindelimiter=','; )

Tom
Super User Tom
Super User

Do really need the list of values stuffed into a macro variable?

It the intent is to just write a note to the log then a data step is probably best.

data _null_;

  set bin_table ;

  where missing(max) ;

  put 'WARNING: There are missing values of MAX in BIN_TABLE.';

  stop;

run;

Anna_nag
Obsidian | Level 7

Hi All,

Thanks for the feedback but I want to use this logic inside macro only.

Jerry- I tried to replace delimiter with space but still it doesn't return anything.

Patrick
Opal | Level 21

Like others already hinted your macro usage approach doesn't appear to be optimal. But then I don't know the full story so below an %if condition syntax which should return what you're after.

data bin_table;

  input max;

  datalines;

1

2

.

4

6

8

;

run;

%macro try/minoperator;

  proc sql noprint;

    select distinct max into: max_val separated by ', '

      from bin_table;

  quit;

  %if %qsysfunc(findc("&max_val",.)) %then

    %do;

      %put "ERROR:Missing value found";

    %end;

%mend try;

%try;

JerryLeBreton
Pyrite | Level 9

Did you fix the %IF condition too?

43 %macro try/minoperator;

44 

45 proc sql noprint;

46 select distinct max into: max_val separated by ' '

47 from bin_table;

48 quit;

49 

50 %if . in &max_val %then %do;

51 %put "ERROR:Missing value found";

52 %end;

53 

54 %mend try;

55 %try;

NOTE: PROCEDURE SQL used (Total process time):

  real time 0.00 seconds

  cpu time 0.00 seconds

  

"ERROR:Missing value found"

56 

57 ;

If you want variation within your preferred macro approach you can try this too:

%macro try/minoperator;

proc sql noprint;

     select count(*) into: max_val

     from bin_table

     where max is missing ;

quit;

%if &max_val  %then %do;

     %put "ERROR:Missing value found";

%end;

%mend try;

Tom
Super User Tom
Super User

I find the INDEXW() function is most useful for testing for a specific value in a delimited list.

%macro try(list);

%if %sysfunc(indexw(&list,.)) %then %put ERROR: Missing value found.;

%mend try;

%try(1 2 3 4);

%try(. 2 3);

%try(2 . 1);

%try(1.2 2.3);

Ksharp
Super User

Also you could count the missing value :

%macro try/minoperator;

proc sql noprint;

     select count(*) into : n

     from bin_table

      where max is missing ;

quit;

%if &n ne 0 %then %do;

     %put "ERROR:Missing value found";

%end;

%mend try;

Xia Keshan

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
  • 9 replies
  • 1390 views
  • 0 likes
  • 7 in conversation