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.
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.
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;
If empty dataset, a message will be assigned to a specific variable (either subject or rand_id) depending on which dataset name.
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?
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.