How to create a do-loop list?

[ybz12003]

Hello,

I would like to create a do-loop list based on the "Health_Department" column of my sample test dataset below. This do-loop macro list will be used to output an Excel file in the next step using ODS. My goal is that the macro list replaces the "Ohio Dept of Health Laboratory" in the ODS steps, then does the list in the "Health department" of the Test dataset one by one. Could you please assist? Thanks.

data test;
Length Health_Department $200 COUNT 8;
input Health_Department $ COUNT  ;
infile datalines delimiter='#';
datalines;
Ohio Dept of Health Laboratory#7913247#
South Dakota Department of Health#7913289#
State of Florida Department of Health#8627568#
Louisiana Department of Health#8627568#
Montana Public Health Laboratory#9627588#
;
run;


ods excel file="Pathway\Unmatch_&tdate..xlsx";
ods excel options(sheet_name="Ohio Dept of Health Laboratory");
Proc report data=Sample;
 	column  Health_Department  Order_Test Updated_Date;
	where Health_Department in ('Ohio Dept of Health Laboratory');
	define Health_Department / group ;
	define Order_Test / group ;
	define Updated_Date / group ;
run;
ods excel close;

[Tom]

Why not just use BY group processing? Check the documentation of the SHEET_NAME option to see how to use the value of a BY variable as the sheet name.

PS You might need to first make sure the values are valid as sheet names.  There is a limit on the length of a sheet name.

[ClarkLawson]

If I was tackling this problem, I would put the PROC REPORT inside a macro and use the WORK.TEST dataset as a control table to loop around using call execute within a datastep.

data test;
Length Health_Department $200 COUNT 8;
input Health_Department $ COUNT  ;
infile datalines delimiter='#';
datalines;
Ohio Dept of Health Laboratory#7913247#
South Dakota Department of Health#7913289#
State of Florida Department of Health#8627568#
Louisiana Department of Health#8627568#
Montana Public Health Laboratory#9627588#
;
run;

%macro report_it(department_name);
ods excel options(sheet_name="&department_name.");
Proc report data=Sample;
 	column  Health_Department Order_Test Updated_Date;
	where Health_Department in ("&department_name.");
	define Health_Department / group ;
	define Order_Test / group ;
	define Updated_Date / group ;
run;
%mend;

data _null_;
set work.test end=last;

  if _n_=1 then do;
    call execute('ods excel file="C:\Temp\Unmatch_&sysdate..xlsx";');
  end;

  length command $1000.;
  command = '%report_it(' || Health_Department || ');';
  call execute(command);

  if end=last then do;
     call execute('ods excel close;');
  end;

run;

[ybz12003]

There are errors shown the logs.

62   %macro report_it(department_name);
63   ods excel options(sheet_name="&department_name.");
64   Proc report data=elc.Nrevss_251_CoV_noupdated;
65       column  epi_message_sender Health_Department eor_spec_id Order_Test Updated_Date;
66       where Health_Department in ("&department_name.");
67       define epi_message_sender / group ;
68       define Health_Department / group ;
69       define eor_spec_id / group ;
70       define Order_Test / group ;
71       define Updated_Date / group ;
72   run;
73   %mend;
74
75   data _null_;
76   set work.CoV_251_NoUpdated_CNT end=last;
77
78       if COUNT < 10000;
79
80     if _n_=1 then do;
81       call execute('ods excel
81 ! file="pathway\Unmatch_&sysdate..xlsx";');
82     end;
83
84     length command $1000.;
85     command = '%report_it(' || Health_Department || ');';
86     call execute(command);
87
88     if end=last then do;
89        call execute('ods excel close;');
90     end;
91
92   run;

NOTE: Variable end is uninitialized.

MLOGIC(REPORT_IT):  Beginning execution.
MLOGIC(REPORT_IT):  Parameter DEPARTMENT_NAME has value Arizona Department of Health Services
SYMBOLGEN:  Macro variable DEPARTMENT_NAME resolves to Arizona Department of Health Services
MPRINT(REPORT_IT):   ods excel options(sheet_name="Arizona Department of Health Services");
MPRINT(REPORT_IT):   Proc report data=elc.Nrevss_251_CoV_noupdated;
MPRINT(REPORT_IT):   column epi_message_sender Health_Department eor_spec_id Order_Test Updated_Date;
SYMBOLGEN:  Macro variable DEPARTMENT_NAME resolves to Arizona Department of Health Services
MPRINT(REPORT_IT):   where Health_Department in ("Arizona Department of Health Services");
MPRINT(REPORT_IT):   define epi_message_sender / group ;
MPRINT(REPORT_IT):   define Health_Department / group ;
MPRINT(REPORT_IT):   define eor_spec_id / group ;
MPRINT(REPORT_IT):   define Order_Test / group ;
MPRINT(REPORT_IT):   define Updated_Date / group ;
MPRINT(REPORT_IT):   run;
MLOGIC(REPORT_IT):  Ending execution.
MLOGIC(REPORT_IT):  Beginning execution.
MLOGIC(REPORT_IT):  Parameter DEPARTMENT_NAME has value Arkansas Department of Health
ERROR: More positional parameters found than defined.
MLOGIC(REPORT_IT):  Ending execution.

[ClarkLawson]

 You should replace 

if COUNT < 10000;

with

where COUNT < 10000;

Also are there some values of Health_Department that contain commas? If so, then that might be where the problem arises.

[Kurt_Bremser]

You must learn to read (Maxim 2) and analyze your log.

NOTE: Variable end is uninitialized.

This tells you that you used the keyword in a place where SAS thinks it is a variable name.

Let's go looking for this.

This is it:

if end=last then do;

Syntactically, you compare the variables end and last. But there is no variable end defined or set anywhere.

But there is this:

set work.CoV_251_NoUpdated_CNT end=last;

where you define the variable last as boolean (numeric, contains only 0 or 1), which is set to 1 when the DATA step reads the last observation of the dataset.

What you most probably wanted is this:

if last then do;

[ballardw · Accepted solution]

Variables not in the data set used in the proc report.

Name of the data set for Proc Report does not match data set created.

How would any of that work?

Addressing the name and missing variables here is an example of @Tom's suggestion and demonstrates your values are likely too long for valid names in Excel for some of the tabs.

data test;
Length Health_Department $200 COUNT 8;
input Health_Department $ COUNT  ;
infile datalines delimiter='#';
order_test = _n_;
updated_date= today();
format updated_date date9.;
datalines;
Ohio Dept of Health Laboratory#7913247#
South Dakota Department of Health#7913289#
State of Florida Department of Health#8627568#
Louisiana Department of Health#8627568#
Montana Public Health Laboratory#9627588#
;

proc sort data=test;
by  Health_Department;
run;

ods excel file="&drive2.\example.xlsx"
    options(sheet_interval='BYGROUP' sheet_name="#byval1");
Proc report data=test;
   column  Health_Department  Order_Test Updated_Date;
/* where Health_Department in ('Ohio Dept of Health Laboratory');*/
   by health_department;
   define Health_Department / group ;
   define Order_Test / group ;
   define Updated_Date / group ;
run;
ods excel close;

The sheet_name="#byval1" works with the BYgroup to just show the value of the first (and only in this case) BY variable as the name.

Since you already have used the abbreviation of Dept in Ohio I would suggest similar for South Dakota and others. Or do a bit of research and find what abbreviation the organization uses. For example Idaho DHW for Idaho Department of Health and Welfare which is also too long for an Excel sheet name.