DATA Step, Macro, Functions and more

What my Macro is Missing?

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

What my Macro is Missing?

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.


Accepted Solutions
Solution
‎12-05-2017 02:47 PM
Super User
Posts: 6,631

Re: What my Macro is Missing?

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.

View solution in original post


All Replies
Super User
Posts: 23,291

Re: What my Macro is Missing?

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;
Contributor
Posts: 23

Re: What my Macro is Missing?

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

Super User
Posts: 6,631

Re: What my Macro is Missing?

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?

Contributor
Posts: 23

Re: What my Macro is Missing?

Posted in reply to Astounding
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).
Contributor
Posts: 23

Re: What my Macro is Missing?

Posted in reply to Astounding
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.
Solution
‎12-05-2017 02:47 PM
Super User
Posts: 6,631

Re: What my Macro is Missing?

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 122 views
  • 0 likes
  • 3 in conversation