topic Re: Macro auto check in SAS Programming

Macro auto check

MadMax — Fri, 29 May 2020 09:17:30 GMT

Good day,

I have 5 customers. (A,B,C,D,E)

* for each customer I have a folder with their monthly orders , named Orders_202001 , Orders_202002 etc.

The problem I have is not all customers started in 202001, some might have started 201905 or 202004. What I want to do is combine each customers order datasets into 1 list , but the code should be the same code. The code needs to look and wherever the order datasets start and end it must know.

Customer A will have Orders_202001 to Orders_202005 the code should be the same code I use for B,C,D and E but I do not want to tell the code when order datasets started and ended, it must know and process all the order datasets where data like "customer_order_%"

I am testing the code only on one customer, if it works for customer A it will work for the rest.

Please can someone try and assist me ?

Re: Macro auto check

Patrick — Fri, 29 May 2020 09:59:20 GMT

You will have to create a directory listing and then you SAS code to process the directories that actually exist.

How to exactly do that will depend on whether your directories are on a Unix/Linus file system or on Windows and if option XCMD is enabled or not (is it XMCD or NOXCMD).

If you're after dynamic code that creates single list of all customers in a single call of the program then you need also to provide the folder names of the customers, like: <some root path>/customer folders/<date folder>/<file name>

If option XCMD is enabled (check via: Proc Options option=xmcd; run;) then first step is to get the listing working out of a command prompt (using DIR for Windows or LS for Unix/Linus).

Re: Macro auto check

s_lassen — Fri, 29 May 2020 18:41:10 GMT

Instead of using an operating system command, as suggested by @Patrick, I would use the DOPEN and DREAD functions.

E.g.:

%macro read_dir(customer);
  Filename cust_dir "/dir/&customer"; /* This should be the actual location of the customer's folders */
  %local I dir_ID foldername month;
  %let dir_ID=%sysfunc(dopen(cust_dir));
  %do I=1 %to %sysfunc(dnum(&dir_ID));
    %let foldername=%sysfunc(dread(&dir_ID,&i));
    %let month=%substr(&foldername,8);
    libname monthly "/dir/&customer/&foldername";
    data temp;
      set monthly.orders; /* or whatever your dataset is called */
      length month $20;
      retain month "&month";
    run;
    proc append base=Cust_&customer data=temp;
    run;
    %end;
  %let dir_ID=%sysfunc(dclose(&dir_ID));
%mend;