topic Re: Macro to read XPT files in SAS Enterprise Guide

Macro to read XPT files

Justin_Lang — Sat, 26 Jun 2021 02:17:12 GMT

Hello,

I am new at using macros. I was given a macro to convert XPT files to a SAS dataset. Here is the code.

%macro drive(dir,ext,out);                                                                                                                                                                                                                                                       
  %let filrf=mydir;                                                                                                                                                                                                                                                  
  /* Assigns the fileref of mydir to the directory and opens the directory */                                                                    
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                                
  %let did=%sysfunc(dopen(&filrf));                                                                                                      
  /* Returns the number of members in the directory */                                                                   
  %let memcnt=%sysfunc(dnum(&did));                                                                                                                                                                                                                                       
   /* Loops through entire directory */                                                                                                  
   %do i = 1 %to &memcnt;                                                                                                                
         /* Returns the extension from each file */                                                                                                                                    
     %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                                                                                                                                                                                                  
     /* Checks to see if file contains an extension */                                                                                     
     %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&ext) %then %do;                                                                                                                                                                                               
     /* Checks to see if the extension matches the parameter value */                                                                      
     /* If condition is true, submit PROC COPY statement  */                                                                      
     %if (%superq(ext) ne and %qupcase(&name) = %qupcase(&ext)) or                                                                       
         (%superq(ext) = and %superq(name) ne) %then %do;                                                           
     	    libname old xport "&dir.\%qsysfunc(dread(&did,&i))"; 
		libname new "&out";
		proc copy in=old out=new;
		run;
     %end;                                                                                                                               
   %end; 
  %end; 
                                                                                                                                        
  /* Close the directory */                                                                                                            
  %let rc=%sysfunc(dclose(&did));                                                                                                        
/* END MACRO */                                                                                                                                           
%mend drive;

When I run the following code

%drive('\appdata\sasusers\JLANG\NHANES data', 'XPT', '\appdata\sasusers\JLANG\NHANES data\DATA')

I receive the following error.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&memcnt
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro DRIVE will stop executing.

The name of the file that Im trying to convert is DEMO_F.XPT

Please let me know what im doing wrong here!

Thanks for your help 🙂

Re: Macro to read XPT files

Patrick — Sat, 26 Jun 2021 04:59:07 GMT

@Justin_Lang

Think of SAS macros as text generators. Your macro is generating SAS syntax (libnames/proc copy) which SAS then executes once the macro has done its job generating the syntax.

Using a macro should be simple if also given the docu how parameter passing needs to look like. It's likely not the ideal macro for a beginner to also get into understanding the macro syntax.

The macro parameters are:

- dir: The source directory the macro will inspect (all sub-directories included!)

- ext: The file extension the macro will look for

- out: The target directory .xpt files will get copied to as .sas7bdat files

The macro will actually only work for .xpt files so there shouldn't be a parameter ext but this should better be hard-coded in the macro (or macro logic changed to also deal with other extensions).

The macro is based on SAS Sample macro %drive() documented here. If you want to understand the macro logic start with the docu and once you've got that look into the version you've have to understand the amendments.

And to answer your question: Pass the parameters without quoting as below:

%drive(\appdata\sasusers\JLANG\NHANES data, XPT, \appdata\sasusers\JLANG\NHANES data\DATA)

...and on 2nd thought: The backslashes indicate a Windows environment but if so then you would either need a drive letter like d:\appdata\.... or it's a unc path which then should look like \\apdata\...

Re: Macro to read XPT files

jimbarbour — Sat, 26 Jun 2021 04:39:37 GMT

In addition to what @Patrick said, this looks like a Windows environment; is that correct? Typically, I see a drive designation preceding the path. Something like: C:\Windows\Containers although maybe \appdata is correct in your situation. Take the single quotes out as @Patrick suggests, and that may be all that needs to be done.

It's probably OK, but you do realize that there are embedded blanks in the path, yes? That isn't necessarily invalid, but I would check that. It could be an underscore. The dir macro variable has to be correct in order for the memcnt macro variable to be correct.

As far as the macro itself is concerned, you've got an invalid memcnt. If the dir variable is invalid as I suspect it is, then %let rc=%sysfunc(filename(filrf,&dir)); will return a non-zero return code indicating an error. Then, the dopen() in the next statement will not work as it tries to assign a directory ID. Then, when the next statement executes, %let memcnt=%sysfunc(dnum(&did)); does not return a valid numeric count because it requires a valid directory ID. Lastly, %do i = 1 %to &memcnt; is failing because memcnt is not a number.

If you fix the directory, all of this may be resolved. If it is not resolved, then we'll have to do some debugging. We'll probably want to put

%PUT NOTE: &=rc after each %LET rc statement so the return codes will be written to the log. Knowing the return codes should go a long way towards debugging the macro. But it may be that all we need is to fix the dir variable, so let's try that first.

I'm also wondering if this statement: %let rc=%sysfunc(filename(filrf,&dir));

really should have an ampersand in front of filrf, like this: %let rc=%sysfunc(filename(&filrf,&dir));

since filrf is a macrovariable, but, again, let's try fixing dir first and see where that takes us.

%macro drive(dir,ext,out);                                                                                                                                                                                                                                                       
  %let filrf=mydir;                                                                                                                                                                                                                                                  
  /* Assigns the fileref of mydir to the directory and opens the directory */                                                                    
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                                
  %let did=%sysfunc(dopen(&filrf));                                                                                                      
  /* Returns the number of members in the directory */                                                                   
  %let memcnt=%sysfunc(dnum(&did));                                                                                                                                                                                                                                       
   /* Loops through entire directory */                                                                                                  
   %do i = 1 %to &memcnt;

Jim

Re: Macro to read XPT files

Patrick — Sat, 26 Jun 2021 04:42:15 GMT

@jimbarbour

Actually tested the macro in a Windows environment and the blank in the folder name is allowed. Once fully qualified valid paths get used and none of the parameters is in quotes the macro works.

Re: Macro to read XPT files

jimbarbour — Sat, 26 Jun 2021 04:45:52 GMT

@Patrick,

OK, good.

I'm still a little surprised that

%let rc=%sysfunc(filename(filrf,&dir));

doesn't need an ampersand preceding the variable filrf, but if it works, I can't argue with that.

Jim

Re: Macro to read XPT files

Patrick — Sat, 26 Jun 2021 04:57:28 GMT

@jimbarbour wrote:

@Patrick,

OK, good.

I'm still a little surprised that
%let rc=%sysfunc(filename(filrf,&dir));
doesn't need an ampersand preceding the variable filrf, but if it works, I can't argue with that.

Jim

@jimbarbour That's how SAS implemented the syntax.

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/n15scht124hr4nn1g296cqg2kqfa.htm

Re: Macro to read XPT files

jimbarbour — Sat, 26 Jun 2021 15:22:45 GMT

@Patrick,

Interesting. I had wondered if it were context sensitive. It's nice to have confirmation. Thank you for that.

~~Even more interesting: It works equally well with an ampersand in front, i.e. &filrf.~~ Edit: Actually, as @Patrick points out, it does not work if the var name is preceded by an ampersand. Thank you to Patrick for catching that.

I couldn't get it to work with just \appdata alone. I had to precede \appdata with a drive designation and path. I'm not sure if that's some kind of local environmental setting or due to the fact that I'm on Win Server 2016 (as opposed to Win 10 or some other flavor of Windows).

Jim

Re: Macro to read XPT files

Patrick — Sat, 26 Jun 2021 06:01:14 GMT

@jimbarbour

"It works equally well with an ampersand in front, i.e. &filrf."

Only because you've made the change after already running without the ampersand in the same SAS session. Do this in a fresh new session and it won't work.

"I had to precede \appdata with a drive designation"

I've already added such a comment to my initial answer. Looks like besides of the quoting the path the op uses isn't valid in a Windows environment.

Re: Macro to read XPT files

Kurt_Bremser — Sat, 26 Jun 2021 06:52:38 GMT

Both the FILENAME and LIBNAME functions have a side effect on the first argument (if the variable is empty, a random value is created and stored in the variable), which is therefore a "call by reference" and not a "call by value". In C, you would use a pointer (&variable) instead of just the variable name.

In a data step, the data step compiler handles that (by converting a literal to a variable under the hood), but if the functions are used in a macro, only a macro variable name must be supplied, not the contents (which you would get when &name is resolved).

The documentation states this explicitly:

In a macro (for example, in the %SYSFUNC function), fileref is the name of a macro variable (without an ampersand) whose value contains the fileref to assign to the external file.

Re: Macro to read XPT files

Kurt_Bremser — Sat, 26 Jun 2021 06:56:32 GMT

If macro variable filrf contains a string which can be used as a valid macro variable name, then that macro variable will be created and receive the randomly generated file reference.

Re: Macro to read XPT files

Ksharp — Sat, 26 Jun 2021 10:40:54 GMT

No need macro .


/*************一个XPT文件 转成 一个SAS数据集************/
/* path to address xpt file*/
%let path_in= D:\XiaKeShan\my_code\数据管理\转XPT文件\XPT  ;
/* path to store sas dataset*/
%let path_out= D:\XiaKeShan\my_code\数据管理\转XPT文件\SAS  ;


/*transform a xpt file into a sas dataaset*/
libname out v9 "&path_out";
data _null_;
rc=filename('x',"&path_in");
did=dopen('x');
do i=1 to dnum(did);
  memname=dread(did,i);
call execute(cat("libname tranfile xport '&path_in\",strip(memname),"';proc copy in=tranfile out=out ;run;"));
end;

run;

Re: Macro to read XPT files

jimbarbour — Sat, 26 Jun 2021 15:20:41 GMT

@Patrick wrote:

"It works equally well with an ampersand in front, i.e. &filrf."

Only because you've made the change after already running without the ampersand in the same SAS session. Do this in a fresh new session and it won't work.

Ah. You're quite right. How could it be otherwise? If one puts the ampersand in front, then SAS will look for a macro variable with the name of whatever &filrf resolves to. I have put a strike through in my earlier comment. Thank you for pointing that out.

Jim

Re: Macro to read XPT files

Justin_Lang — Sat, 26 Jun 2021 16:40:22 GMT

@Patrick & @jimbarbour

Im still running into the same issues. I added the D : and tried the // for my directory. I also tried moving the directory to my personal C:. The problem, i believe, is that my SAS program is on a secure network. If I run the following code I am able to read the XPT file into a SAS format.

LIBNAME IN XPORT '/appdata/sasusers/JLANG/NHANES_data/DEMO_F.XPT';
LIBNAME OUT '/appdata/sasusers/JLANG/NHANES_data/DATA';

proc copy inlib=IN outlib=out;
	run;

This code works perfectly. It may seem odd, but that is the call for the directory on our network. We don't need to add the D : or the //.

Just a reminder, the error that I receive is the following

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       &memcnt 
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro DRIVE will stop executing.

The macro that I am trying to use would read multiple XPT files into SAS datasets at the same time.

Re: Macro to read XPT files

Kurt_Bremser — Sat, 26 Jun 2021 17:05:58 GMT

So you should call the macro as

%drive(/appdata/sasusers/JLANG/NHANES_data,XPT,/appdata/sasusers/JLANG/NHANES_data/DATA)

and change the backslash in the LIBNAME statement to a slash. Set

options mprint mlogic symbolgen;

before calling the macro, and post the log if it does not work.