topic Re: Database Query and the Specific File extraction in New SAS User

Database Query and the Specific File extraction

dwilliams1 — Mon, 11 Mar 2019 15:58:41 GMT

I want to do the following:

1) Connect to a MS Access database (Successfully done)

2) Create data table of all runs as read from specific table in database (Successfully done)

3) Create data table of the 10 most recent runs (Successfully done)

4) Using proc sql, create a space deliminated list of these runs (Successfully done)

5) Loop over the list and create data tables in the work directory of the most recent 10 runs. (Fails Miserably)

Here's a snippet of the failed part from the log:

319 data _all_; /* Fails miserable */

320 set

321 %do i=1 %to %word_count2(&ParamList);

ERROR: The %DO statement is not valid in open code.

322 &ParamList

323 %end;

ERROR: The %END statement is not valid in open code.

324 ;

325 run;

Re: Database Query and the Specific File extraction

dwilliams1 — Mon, 11 Mar 2019 16:00:53 GMT

Attached is the sas code. Thought it was included initially.

Re: Database Query and the Specific File extraction

ballardw — Mon, 11 Mar 2019 19:57:04 GMT

@dwilliams1 wrote:

I want to do the following:

1) Connect to a MS Access database (Successfully done)

2) Create data table of all runs as read from specific table in database (Successfully done)

3) Create data table of the 10 most recent runs (Successfully done)

4) Using proc sql, create a space deliminated list of these runs (Successfully done)

5) Loop over the list and create data tables in the work directory of the most recent 10 runs. (Fails Miserably)

Here's a snippet of the failed part from the log:

319 data _all_; /* Fails miserable */

320 set

321 %do i=1 %to %word_count2(&ParamList);

ERROR: The %DO statement is not valid in open code.

322 &ParamList

323 %end;

ERROR: The %END statement is not valid in open code.

324 ;

325 run;

The message "ERROR: The %DO statement is not valid in open code." means that you need to create a proper macro using the

%macro to %mend statements to define macro code and then execute the defined macro. Some of the later versions of SAS support

%do in "open code", which means outside of a %macro/%mend code block, but apparently not yours.

If your &parmlist contains space delimited proper library.dataset names then the %do is not needed as the set statement accepts multiple data sets. Your code, as written would likely have generated multiple copies of each data set in your list.

Not that the following will make a particularly useful data set it demonstrates use of SET statement with multiple sets:

data work.junk;
   set 
      sashelp.class
      sashelp.cars
   ;
run;

If your PARAMLIST variable looks like proper library dataset names, or all of the sets are in the WORK library then

data _all_;
   set 
      &Paramlist
   ;
run;

Should work.