topic Re: Creating a XPT macro in SAS Programming

Creating a XPT macro

bharath86 — Wed, 08 Sep 2021 15:37:35 GMT

Hi,

I am trying to create a macro for converting sas7bdat files to XPT's in a batch. Please find the attached code.

Please help me debug the code. it doesnt produce the output and no warnings in log.

/*Macro for converting SAS7bdat to xpts */
%macro xpmac(sasfloc=,ioutploc=,);

/*To get list of files in directory*/
data tdata;
	length fname $300.;
	rc=filename("filrf","&sasfloc");
	did=dopen("filrf");
	memcount=dnum(did);
	if memcount>0 then do;
	do i=1 to memcount;
	fname=upcase(dread(did,i));
	if index(trim(left(upcase(reverse(fname)))),'TDB7SAS.')=1 then output;
	end;
end;
else stop;
rc=dclose(did);
run;

%let sasfcount=0; 

/*Assign in macro variable for naming the sas7bdat file*/
data new;
	set tdata end=last;
	call symput('sasbfile'||left(_n_),trim(left(fname)));
	if last then call symput('sasfcount',left(_n_));
run;

/*library for output dataset location;*/
libname libout xport "&ioutploc";
/*Do Loop for each sas7bdat file;*/
%do lindx=1 %to &sasfcount; 

/*Assign file reference to the sas7bdat file*/
filename ftemp "&sasfloc\&&sasbfile&lindx";

/*Reading the first line of the sas7bdat file*/
data _null_;
	length firstline $15.;
	infile ftemp obs=1;
	input firstline;
	if index(upcase(firstline),'HEADER')>0 then call symput('lxpttype','COPY');
run;

%if &lxpttype=COPY %then %do;

	
	libname libin "&sasfloc\&&sasbfile&lindx";

	/*For converting xpt to datasets*/
	proc sql noprint;
	  create table _tmembers as select memname, "&&sasbfile&lindx" as source length 200 from dictionary.tables where libname='LIBIN';
	  (select memname from dictionary.tables where libname='LIBIN');
	quit;
	proc copy in=libin out=libout memtype=data; 
	run;
	
	%end;
%end;

%mend xpmac;

%xpmac(sasfloc=,ioutploc=)

Thank you

Bharath

Re: Creating a XPT macro

Reeza — Wed, 08 Sep 2021 15:57:39 GMT

Well you called the macro without any parameters so it definitely won't work.

%xpmac(sasfloc=,ioutploc=)

What happens if you add the macro debugging options and run it with file paths?

options mprint symbolgen;

*rest of your code....

Re: Creating a XPT macro

bharath86 — Wed, 08 Sep 2021 16:02:26 GMT

Hi, I wantedly removed my personal folder path name, incase if someone wants to try they can input the parameters.

If you want to run it on your pc pplease provide the path and see if it works.

Re: Creating a XPT macro

Tom — Wed, 08 Sep 2021 16:41:21 GMT

So you want to convert SAS7BDAT files into XPORT files?

You don't need to hunt for SAS7BDAT files. Let SAS do that. Just point a libref at the directory.

%macro xpmac(sasfloc=,ioutploc=);

libname inlib "&sasfloc";
libname outlib xport "&ioutploc";
proc copy inlib=inlib outlib=outlib;
run;

%mend;

So then just call the macro with an source directory and a target filename:

%xpmac
(sasfloc=c:\mydata\lib1
,ioutploc=c:\mydata\lib1.xpt
);

Also:

You have a step that appears to be trying to determine something from reading the beginning of the SAS7BDAT file. That is something you would need to do if you were trying to go the other way. The XPT file extension has no real intrinsic meaning. People can use if for any type of file they want. So they could create an XPORT file using the the XPORT engine and call it a .xpt file. Or they could use PROC CPORT to create the file instead. Or they could use the SAS supplied %LOC2XPT() macro to create an XPORT file using the extended V8/V9 definition that the XPORT engine cannot read or write.

The corresponding test you might want to do is to check if the SAS datasets in the directory have any features that would make them incompatible with the XPORT engine. Things to check: Name longer than 8 bytes. Length larger than 200 bytes. FORMAT name or INFORMAT name longer than 8 bytes. Label or Memlabel longer than 40 bytes. Non standard member name or variable name.

Re: Creating a XPT macro

bharath86 — Wed, 08 Sep 2021 16:58:06 GMT

what if I try to simplyfy the complete code this way.

This still doesnt give me the output, i have checked all the parameters as you mentioned but it still doesnt produce the XPT file and log doesnt warn anything.

can you please try this code and see if it works for you.

%Macro ds();
Libname Raw_sets "D:\Data";
Libname Raw_xpt xport "D:\Data\New folder\&ds..xpt";

proc copy in=Raw_sets out=Raw_xpt;
select &ds;
run;

%Mend;

%ds(ae);

Re: Creating a XPT macro

Tom — Wed, 08 Sep 2021 17:32:35 GMT

And what does the SAS log tell you is wrong? Did you turn on the MPRINT option so you can see the SAS code that the macro generated.

Where did you define the macro variable DS your code is referencing?

Why are you trying to pass a parameter value to macro that does not have any parameters defined?

%macro ds(ds);
libname raw_sets "D:\Data";

%if not %sysfunc(exist(raw_sets.&ds)) %then %do;
  %put ERROR: Dataset RAW_SETS.&ds does not exist ;
%end;
%else %do;

libname raw_xpt xport "D:\Data\New folder\&ds..xpt";

proc copy in=Raw_sets out=Raw_xpt;
select &ds;
run;
%end;
%mend ds;

%ds(ae);