Exporting multiple SAS datasets with some editing into multiple csv files

[SimonLowe]

I've been using a code with seemingly no problem for a quite a while now, and today it stopped working and in a pretty weird way.

I have several SAS files in a folder and I want to export each of them into a CSV file, while keeping only some columns and if some condition is satisfied.

The file structure is folders called data_(year) and inside are several files called file_(region number)

%macro extract_data(year);
libname lib "c:\data_&year.";

%let file_names = 24 27 29;
%let nb_files = 3;

%do i=1 %to &nb_files.;

   %let next_file = %scan(&file_names, &i);

   data file&next_file. (keep = C1 C2)
      set lib.file_&next_file.;
      if C1 = &next_file.;
   run;

   proc export data = file&next_file.
               outfile = "c:\Export\file&year._&next_file."
               dbms = csv
               replace;
%end;
%mend;

%extract_data(2010)

Today this stopped working and the error I get is for the FIRST file where I get an "ERROR 180-322: Statement is not valid or it is used out of proper order" underlining set. The really weird thing is that it does this only for the first file (ie in the example above 24) and then the other exports seem to go through. I tried with different triplets of file_names and now the one which didn't work before does (ie file_names = 12 24 27 and in this case 24 exported normally and it was 12 that didn't work).

Thank you very much for your help!

[ErikLund_Jensen]

Hi @SimonLowe 

You need a semicolon after the data statement, before set.

[SimonLowe]

Ah yes sorry that was a typo on my end! I have a semi-colon. Very sorry about that!

[ballardw]

Any time you get an error you should share the LOG with the error. Copy from the log the code that generated the error along with all the notes, messages, warnings and/or errors generated by that data step or procedure call. On the forum open a text box using the </> icon that appears above the message box and paste the text.

The text box is important to preserve the formatting of any of the diagnostics that SAS provides with many messages.

Since you are doing this with macro code you may need to set OPTIONS MPRINT; to get the details of exactly when this happens.

Note: It is a very good idea to always properly end any procedures or data steps inside a macro with the appropriate Run; or Quit; if needed. Since macros generate code you do not want to take a chance that the last code generated by your macro could allow something else to be treated as part of the code generated.

Your %let File_names really should be a parameter of the macro and you can COUNT the number of items in it.

Manually changing code in the middle of a macro that supposedly works, is one of the causes minor errors that create headaches. Place things that you change often, such as the Year and list of 'names' as parameters, and that will reduce the chance of mangling the code. Calculate dependencies, such a the number of files, from the values when practical.

%macro extract_data(year=, File_names=);
libname lib "c:\data_&year.";

%do i=1 %to  %sysfunc(countw(&file_names.));

   %let next_file = %scan(&file_names., &i);

   data file&next_file. (keep = C1 C2) ;
      set lib.file_&next_file.;
      if C1 = &next_file.;
   run;

   proc export data = file&next_file.
               outfile = "c:\Export\file&year._&next_file."
               dbms = csv
               replace;
  run;
%end;
%mend;

%extract_data(year=2010, File_names=24 27 29)

Is there any particular reason you don't assign a file extension, such as CSV to the OUTFile name?

[Tom]

Most likely your macro variable has some invisible character in it.

Let's demonstrate by making a similar macro and some made up dataset.

%macro example(file_names);
%local i next_file ;
%do i=1 %to %sysfunc(countw(&file_names));

   %let next_file = %scan(&file_names, &i);

   data file&next_file. ;
      set work.file_&next_file;
   run;

%end;
%mend;


data file_12 file_24 file_36 ;
  set sashelp.class(obs=1);
run;

Now let's call it with a list that includes the "non-breaking" space in front of the first value in the list.

data _null_;
  string='A0'x||'12 24 36';
  call symputx('list',string);
run;
options mprint;
%example(&list)

Result

23445  %example(&list)
MPRINT(EXAMPLE):   data file 12 ;
MPRINT(EXAMPLE):   set work.file_ 12;
ERROR: The value FILE_ 12 is not a valid SAS name.
MPRINT(EXAMPLE):   run;

ERROR: The value FILE 12 is not a valid SAS name.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


MPRINT(EXAMPLE):   data file24 ;
MPRINT(EXAMPLE):   set work.file_24;
MPRINT(EXAMPLE):   run;

NOTE: There were 1 observations read from the data set WORK.FILE_24.
NOTE: The data set WORK.FILE24 has 1 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


MPRINT(EXAMPLE):   data file36 ;
MPRINT(EXAMPLE):   set work.file_36;
MPRINT(EXAMPLE):   run;

NOTE: There were 1 observations read from the data set WORK.FILE_36.
NOTE: The data set WORK.FILE36 has 1 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

[SimonLowe]

Thank you for your answer!

I tried unquote, and that didn't work, I get the same error.

The print didn't really add any information. I'm sending you a screenshot of the error messages (I have edited out some paths and sorry the names aren't exactly the same)

I don't know if that helps ...

Post24 definitely exists. When after that it reads in the next files, it does not have the .DATA afterwards could that be it?

[ballardw]

It certainly does not help us believe that the code you showed earlier is what you are running when you show different names for the macro(s) executed.

I can't copy the part that I think may be part of the issue because you pasted a picture instead of text. If you think your file paths are so sensitive the make the text and type over it with XXX or ZZZ or similar.

In your picture is a line that appears to start with two *.  Something like

*  *  data post

which may be partially acting as a comment meaning the DATA did not execute and the first line of code actually attempting to execute is the Set statement.

If I run this code:

* data junk;
set sashelp.class;
where sex='F';
run;

This is the log:

194  * data junk;
195  set sashelp.class;
     ---
     180

ERROR 180-322: Statement is not valid or it is used out of proper order.

196  where sex='F';
     -----
     180

ERROR 180-322: Statement is not valid or it is used out of proper order.

197  run;

Same error message and quite likely for the same reason: The Data statement did not execute.

Additional evidence: There is no MPRINT output showing that the Data post&nextregion did not resolve to

Data post24. Note that it does show where the SET statement has the lib.post&nextregion resolve.

Note: it is bad idea to have loose * characters inside macros. If you want to comment something use either the /* */ or the %* <text ending in > ; comment.

[Tom]

From the photograph the offending comments appear before the call to the macro, not in the macro definition.

There is no reason not to use statement comments inside of macro definitions.  Statement comments are just as valid inside a macro as any other statement that you want the macro to generate.

A statement like this:

*%extract();

will not prevent the %EXTRACT() macro from running.  It merely places the text that the macro generates into the statement comment that the * started.  And if the macro generate multiple statement then only the first statement generated becomes part of the comment.

If you want to comment out macro calls either convert them to macro comments.

%*extract();

Or wrap them in a block comment.

/*
%extract();
*/

[Tom]

If your code is using %QSCAN() instead of %SCAN() like this:

%let next_file = %qscan(&file_names, &i);

then it might be macro quoting that is causing the issue.

Example:

23465  %example(%str( 12 24))
NOTE: Line generated by the macro variable "NEXT_FILE".
1       file12
            --
            22
             --
             200
MPRINT(EXAMPLE):   data file12 ;
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: Line generated by the macro variable "NEXT_FILE".
1       work.file_12
                  --
                  22
                   --
                   200
MPRINT(EXAMPLE):   set work.file_12;
ERROR: File WORK.FILE_.DATA does not exist.
MPRINT(EXAMPLE):   run;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;, CUROBS, END, INDSNAME, KEY,
              KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

It might be worth adding %UNQUOTE() function to remove the macro quoting form the value.

 data %unquote(file&next_file. )(keep = C1 C2) ;
      set %unquote(lib.file_&next_file.);