Question about using %include in a macro to include another macro

[Reeza]

You use a wild card in your infile statement.

For example

infile 'C:\localdata\*.txt';

points to all files in the folder C:\localdata that end with the extension .txt.

If you want the file name as well, the filename option can be added on to provide the filename.

If you're file has headers in each row, you'll want to ignore those.

Try running Tom's code and see if it works. It's the correct solution to your problem.

[Tom]

So the issue is that the data in the list of files that you are importing contains characters that you would have to quote using macro quoting functions like %QUOTE, %BQUOTE etc if you typed them into you program file.  So when you used them to generate %LET statements they caused trouble. Now that you are using them to generate other SAS code you are seeing the problems in that code.

In particular you are using

  datafile="&FilePath.\&&campy&i.."

and

  create table campy&i as

So if the name of the source file contains some strange characters you might have trouble with the DATAFILE= or if more likely with using the name as the name of SAS table.

SAS tablenames can by up to 32 characters, digits and underscores and must not start with a digit.

Note that your loops for CSV and PIPE are not consistent with each.  Since the only difference between the two is the value used for the DELIMITER in the PROC IMPORT you can simplify your logic.

%do i=1 %to &ncampaigns.;

proc import datafile="&FilePath.\&&campy&i.."

  out=tempy&i

  dbms=dlm replace;

  getnames = no;

  datarow=1;

%if &Data_type ="csv" %then %do;

   delimiter=',';

%end;

%if &Data_type ="pipe" %then %do;

   delimiter='|';

%end;

run;

proc sql;

  create table campy&i as

    select a.* from tempy&i a

    where var1 ne .

    order by var2

  ;

  drop table tempy&i ;

quit;

%end;

[Tom]

To protect against strange characters you might want to use

datafile="&FilePath.\%superq(campy&i)"

[Doxastic]

When I try this code, only one column gets imported.  There is definitely more than one column.... I can't figure out why that's happening.

[Tom · Accepted solution]

Sounds like the delimiter is not getting set. If you have option MPRINT turned on you can see the generated PROC IMPORT code in the SAS log.  NOte that if the value of macro variable does not include the double quotes then you do not want to use them in the %IF condition.

So if you set TYPE to csv.

%let type=csv;

Then this test will be false.

%if &type = "csv" %then ....

[Doxastic]

That helped!  I think that was a big part of the problem.

Now, what if I just took some of your suggestions and use my slightly more user-friendly code... and do something like this.  Part of the problem is a pipe delimited has a different start row and different column definitions.... so they too have to be dynamic.  But I'll just put pipe file in a different folder from csv's (unless the two can go in the same folder.... will SAS read ONLY the .csv files in a folder when I use the wild card?):

%macro Import_Campaigns(FilePath, ncampaigns, Data_type);

%if %trim(&Data_type.) =csv %then %do;

proc import datafile="&FilePath.\*.csv"

  out=tempy&i

  dbms=dlm replace;

  getnames = no;

  datarow=1;

  delimiter=',';

  data campaigns_rerun(keep= HH_id  airing_date live_offset Network daypart); set

%read2;

      rename var6 = HH_id;

      rename var4 = airing_date;

      rename var7 = live_offset;

   rename var16 = Network;

   rename var20 = daypart;

run;

%end;

%if %trim(&Data_type.) =pipe %then %do;

proc import datafile="&FilePath.\*.txt"

  out=tempy&i

  dbms=dlm replace;

  getnames = no;

  datarow=4;

  delimiter='|';

  data campaigns_rerun(keep= HH_id  airing_date live_offset Network daypart); set

%read2;

      rename var1 = HH_id;

      rename var2 = airing_date;

      rename var3 = live_offset;

   rename var4 = Network;

   rename var5 = daypart;

run;

%end;

%mend Import_Campaigns;

[Tom]

You will be able to get PROC IMPORT to read the multiple CSV files as one using the wildcard in the DATAFILE setting since you are not trying to skip any header lines.

But if you try to get PROC IMPORT to read multiple pipe delimited files as one it will only skip the first three lines of the first file, since it is treating all of the files as one file.

Let me state again.  Since your program is not depending on most of the features of PROC IMPORT why not just write your own data step to read the files.  You can start with the one that PROC IMPORT generates, but it usually generates a program that is much more complicated than you need if you already know what data is in your files.

[Doxastic]

Also, in the end this is what I'd like the macro to look like:

%macro Import_Campaigns(FilePath, ncampaigns, Data_type);

%get_filenames(&FilePath.);

%Create_Mac_Var(campy, memname);

%if &Data_type.="csv" %then %do; %readcampaigns(1,&ncampaigns.,csv); %end;  /*  readcampaigns would be the last bit of the main macro, itself macro-tized.  */

%if &Data_type.="pipe" %then %do; %readcampaigns(1,&ncampaigns.,pipe); %end;

%mend Import_Campaigns;

See how much easier this is to read and understand?  It also makes it so I can use all 3 of those macros in other places really easily.

This is why I use macros within macros.

[Reeza]

There's nothing wrong with this structure, but you need to make sure each component/module works on its own first and then the whole.

My other issue is you assume what the output files are named, which isn't a convention I like to use as it makes it harder to reuse components across different programs.

Also, if you are trying to read all files from a folder, SAS has much more efficient ways of doing it than this as Tom has pointed out. Its really worth learning how to do it as your code will be cleaner and much easier to debug in the future.

[jakarman]

The path to hell is paved with good intentions. @doxastic the intention a good one as you have shown the wanted goal to achieve.
The coding habits to achieve that is not that good. Macro-codes should be easy to read pieces and all easy to debug ones.

You can define a bunch of subset macros while using just one master macro. The trick is to define all those in the only source code named as "master" like:

source member (autocall or include) master:

/*  comment describing your macro */

%macro master  ..... ;

   ...

  %mend;        

/*  comment used dedicated subroutines */

%macro subblock1  ..... ;

   ...

  %mend;         

%macro subblock2  ..... ;

   ...

  %mend;  

And several will complain that doing it this way they cannot find all interactions of macro-s and code.

Working on this you can easily check and debug modifications.
One missed quoted ; or other nesting language element will cause your described troublesome behavior.

The macro language processor does the preprocessing somewhat different as the datastep/proc language. In rare situations having unexpected behavior.

Remember the macro references do not need the semicolon as that has a meaning at the other level. Macro quoting is an other area of language conflict solving.
The scope of macrovariables can be a pitfall, this is an automatic process with some conventions.

The generic  advice don not use defining macro-s within macro-s (some exceptions exist) however using macro-s within macro-s is allowed  practice.