What my Macro is Missing?

[mehong19]

I wanted to put a message to a specific variable in a specific dataset if that dataset is empty. I wrote the program below and got error messages in log. Could someone help me with it? Many thanks!

/*program*/

%let num_obs=;

%macro check_empty_dataset(ds=);

proc sql noprint;

select count(*) into :num_obs from &ds;

quit;

%put &=num_obs;

%if &ds in ("Chk_pk_ppd_edc_data_pknd_2") %then %do;

%if &num_obs = 0 %then %do;

data tmp;

length rand_id $100;

rand_id = '*** NO RESULTS FOR THIS PK SAMPLE CHECK ***';

run;

data &ds;

set tmp &ds ;

*keep note;

run;

%end;

%end;

%mend;

/*Log*/

MLOGIC(CHECK_EMPTY_DATASET): Beginning execution.

MLOGIC(CHECK_EMPTY_DATASET): Parameter DS has value Chk_pk_ppd_edc_data_pknd_2

MPRINT(CHECK_EMPTY_DATASET): proc sql noprint;

SYMBOLGEN: Macro variable DS resolves to Chk_pk_ppd_edc_data_pknd_2

MPRINT(CHECK_EMPTY_DATASET): select count(*) into :num_obs from Chk_pk_ppd_edc_data_pknd_2;

MPRINT(CHECK_EMPTY_DATASET): quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

　

MLOGIC(CHECK_EMPTY_DATASET): %PUT &=num_obs

SYMBOLGEN: Macro variable NUM_OBS resolves to 0

NUM_OBS= 0

SYMBOLGEN: Macro variable DS resolves to Chk_pk_ppd_edc_data_pknd_2

ERROR: Required operator not found in expression: &ds in ("Chk_pk_ppd_edc_data_pknd_2")

ERROR: The macro CHECK_EMPTY_DATASET will stop executing.

MLOGIC(CHECK_EMPTY_DATASET): Ending execution.

[Reeza]

What happens in this step if the table is empty or doesn't exist?

proc sql noprint;
select count(*) into :num_obs from &ds;
quit;

[mehong19]

If empty dataset, a message will be assigned to a specific variable (either subject or rand_id) depending on which dataset name.

[Astounding]

The syntax for IN is different in macro language vs. the DATA step.  You would also need to properly set two options, MINOPERATOR and MINDELIMITER.  Why not get rid of IN, and just use an equal comparison?  Do you really need to compare to a list of data set names?

[mehong19]

I have a list of datasets (about 10 datasets) to check if they are empty or not. Depending on the dataset name, the empty-dataset text will be assigned to a different variable (ie. subject or rand_id).

[mehong19]

I tried. But the output is not as expected. The program and log are as below.

1071 %let num_obs=;
1072 %macro check_empty_dataset(ds=);
1073 proc sql noprint;
1074 select count(*) into :num_obs from &ds;
1075 quit;
1076 %put &=num_obs;
1077
1078 %if &ds = "Chk_pk_ppd_edc_data_pknd_2" %then %do;
1079 %if &num_obs = 0 %then %do;
1080 data tmp;
1081 length rand_id $100;
1082 rand_id = '*** NO RESULTS FOR THIS PK SAMPLE CHECK ***';
1083 run;
1084
1085 data &ds;
1086 set tmp &ds ;
1087 *keep note;
1088 run;
1089 %end;
1090 %end;
1091 %mend;
MPRINT():

1092 %check_empty_dataset(ds=Chk_pk_ppd_edc_data_pknd_2);
MLOGIC(CHECK_EMPTY_DATASET): Beginning execution.
MLOGIC(CHECK_EMPTY_DATASET): Parameter DS has value Chk_pk_ppd_edc_data_pknd_2
MPRINT(CHECK_EMPTY_DATASET): proc sql noprint;
SYMBOLGEN: Macro variable DS resolves to Chk_pk_ppd_edc_data_pknd_2
MPRINT(CHECK_EMPTY_DATASET): select count(*) into :num_obs from Chk_pk_ppd_edc_data_pknd_2;
MPRINT(CHECK_EMPTY_DATASET): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


MLOGIC(CHECK_EMPTY_DATASET): %PUT &=num_obs
SYMBOLGEN: Macro variable NUM_OBS resolves to 0
NUM_OBS= 0
SYMBOLGEN: Macro variable DS resolves to Chk_pk_ppd_edc_data_pknd_2
MLOGIC(CHECK_EMPTY_DATASET): %IF condition &ds = "Chk_pk_ppd_edc_data_pknd_2" is FALSE
MLOGIC(CHECK_EMPTY_DATASET): Ending execution.

[Astounding · Accepted solution]

Macro language wouldn't need quotes around a string.  Removing the quotes should work for just a single data set name.

Here's a brief example that shows how to use the proper options to compare to a list of values:

http://support.sas.com/kb/35/591.html

I'm sure you can find additional examples if you need them.  Do not add quotes, and do not insert spaces on either side of your delimiter.