topic Re: Adding variables to macro in SAS Enterprise Guide

Adding variables to macro

OscarBoots1 — Tue, 17 Oct 2017 01:25:45 GMT

Hi Forum,

I'm trying to understand where to add my parameters for the below macro.

I understand that I need to declare variables - Directory paths & filenames etc but I'm not clear where I should with this one.

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docsetVersion=9.4&locale=en

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;          

   %let filrf=mydir;    
   %let rc=%sysfunc(filename(filrf,&dir)); 
   %let did=%sysfunc(dopen(&filrf));
    %if &did ne 0 %then %do;   
   %let memcnt=%sysfunc(dnum(&did));    

    %do i=1 %to &memcnt;              
                       
      %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

Example on the fist line -

%macro drive(dir,ext);

Should I be changing dir for the full directory path , and the file extension for ext?

I also don't get how filrf gets it value?

%local cnt filrf rc did memcnt name;

Can anyone clarify?

Thanks

Re: Adding variables to macro

Reeza — Tue, 17 Oct 2017 01:33:39 GMT

You don't modify that section at all. You call the macro.

Here's a base example.

%macro print(dsetin=);

proc print data=&dsetin;
run;

%mend;

%print(destin = sashelp.class);
%print(destin = sashelp.cars);

The two parameters there are:

1. dir -> directory of where the files are stored.

2. ext -> extension of files of interest, in this case XLSX

Re: Adding variables to macro

OscarBoots1 — Tue, 17 Oct 2017 01:43:00 GMT

Thanks Reeza but i still don't follow.

I'm used to VBA where you write a macro & then Call it.

But any variables need to be Declared, where are the values declared in the script OR do I need to see the Macros own script so I can see what it's doing?

Thanks

Re: Adding variables to macro

Reeza — Tue, 17 Oct 2017 02:28:41 GMT

SAS doesn't require a declaration of variables.

The full macro script is there.

Re: Adding variables to macro

Reeza — Tue, 17 Oct 2017 02:29:19 GMT

Here's a good tutorial on macros:

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Re: Adding variables to macro

OscarBoots1 — Tue, 17 Oct 2017 03:26:17 GMT

Thanks Reeza I'll have a read.

Cheers

Re: Adding variables to macro

Kurt_Bremser — Tue, 17 Oct 2017 07:31:59 GMT

From your link, this is the important section:

Invoke the macro. The first parameter is the directory where the files exist. The second parameter is the extension of the files that you are importing.

%drive(c:\temp,csv)

That's all you have to do. Everything before that is just a step-by-step description how the macro works. It is only of interest if you want to learn writing macros yourself. But before that, you must familiarize yourself with all the Base SAS functions used.

Re: Adding variables to macro

OscarBoots1 — Tue, 17 Oct 2017 22:03:24 GMT

Thanks Kurt,

I replaced the 'dir' with my UNC path to a Network Folder & xls as the file type.

I'm getting an error as the path is > 32 characters.

So this what my code looks like at the start;

 %macro drive("\\abc\def\ghi\My_Report","xls"); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;

By the way I've tried Apostrophes & Quotes top wrap the path.

Any suggestions how I can replace the path?

Do I add a line that says dir = 'Path_name' - for example?

Re: Adding variables to macro

Reeza — Tue, 17 Oct 2017 22:21:16 GMT

YOU DO NOT CHANGE ANYTHING BETWEEN THE MACRO AND MEND STATEMENTS.

Unless you're trying to write your own, which I don't think you are.

You use the macro by calling the name.

%drive(\\abc\def\ghi\My_Report,xls);

Re: Adding variables to macro

OscarBoots1 — Tue, 17 Oct 2017 22:50:49 GMT

Gotcha, thanks Reeza,

So it's similar to what I've used in SQL Server a tvf or svf.

Thanks

Re: Adding variables to macro

OscarBoots1 — Tue, 17 Oct 2017 23:02:16 GMT

One more for you, I want the .xls file to be imported into my SAS EG Project as a table.

The macro is running but I'm not sure whether I should use apostrophes, Quotes or nothing.

& I'm not getting any output?

Thanks

Re: Adding variables to macro

Reeza — Wed, 18 Oct 2017 00:10:19 GMT

That depends on the macro. I can’t see your code so I have no idea of what you ran or what to expect.

If you’re using the code from the appendix that’s only the list of files. The full I linked to is required to import all the files. Or something along those lines.

Re: Adding variables to macro

OscarBoots1 — Wed, 18 Oct 2017 00:56:12 GMT

Ok, I've posted the full code I'm running, problem is no tables imported into EG?

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;          

   %let filrf=mydir;    
   %let rc=%sysfunc(filename(filrf,&dir)); 
   %let did=%sysfunc(dopen(&filrf));
    %if &did ne 0 %then %do;   
   %let memcnt=%sysfunc(dnum(&did));    

    %do i=1 %to &memcnt;              
                       
      %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                    
                    
      %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
       %if %superq(ext) = %superq(name) %then %do;                         
          %let cnt=%eval(&cnt+1);       
          %put %qsysfunc(dread(&did,&i));  
          proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt 
           dbms=xls replace;            
          run;          
       %end; 
      %end;  

    %end;
      %end;
  %else %put &dir cannot be open.;
  %let rc=%sysfunc(dclose(&did));      
             
 %mend drive;
 
%drive('F:\Data\Reports\Test_Reports','xls')

Re: Adding variables to macro

Reeza — Wed, 18 Oct 2017 01:11:30 GMT

You definitely don't need quotes in the macro call.

A macro is more like a stored procedure in SQL that you can call with parameters.

Set the following options before the macro call, rerun it and then check your log. You'll see the exact code that's being submitted. If the code doesn't look like valid SAS syntax it can't run.

options mprint symbolgen;

Re: Adding variables to macro

OscarBoots1 — Wed, 18 Oct 2017 01:34:21 GMT

Thanks I did that.

The code looks OK but I'm getting an error saying the dir path cannot be opened?

Re: Adding variables to macro

OscarBoots1 — Wed, 18 Oct 2017 01:37:53 GMT

Also under SYMBOLGEN in the log, I'm getting DID resolves to 0.

I'm using an UNC path without any Quotes or Apostrophes.

Any ideas?

Re: Adding variables to macro

OscarBoots1 — Wed, 18 Oct 2017 03:10:21 GMT

I've now changed the Directory path Folder to C:\Temp\csv.

I still get the error;

SYMBOLGEN:  Macro variable DIR resolves to C:\Temp\csv
C:\Temp\csv cannot be open.

Re: Adding variables to macro

Reeza — Wed, 18 Oct 2017 04:37:39 GMT

Post the full code and log.

Re: Adding variables to macro

Kurt_Bremser — Wed, 18 Oct 2017 05:50:53 GMT

If DID resolves to zero, the dopen did not work. Which means that the resource in the filesystem is not accessible from the context of the SAS session.

If your SAS runs on a server, local directories on your desktop will not be accessible. And UNC resources available on your desktop may not be there on your server. Or you have different permissions when trying access from the server.