BookmarkSubscribeRSS Feed
bharath86
Obsidian | Level 7

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

5 REPLIES 5
Reeza
Super User
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....
bharath86
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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.

 

bharath86
Obsidian | Level 7

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);
Tom
Super User Tom
Super User

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);

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 888 views
  • 0 likes
  • 3 in conversation