topic Re: convert xpt files to sas datasets in SAS Programming

convert xpt files to sas datasets

lc7033907 — Sat, 29 May 2021 00:14:57 GMT

Hello all,

I have a question about converting xpt files to sas datasets. I have a folder containing a couple of xpt files. now I want to convert them to sas datasets. Of course I can use

proc copy inlib=  outlib= ;
run;

to convert one by one. However, if I have hundreds of xpt files, it's obviously time-consuming. So I am thinking to do this job with a macro or a loop. But I don't know how to do it. Any suggestion is welcome. Thanks.

xpt files saved under

libname xfolder "C:\statdata\onco\datasets\sdtmdata\validation\xpt_new";

output datasets want to save under

libname sfolder "C:\statdata\onco\datasets\sdtmdata\output";

Re: convert xpt files to sas datasets

Sajid01 — Sat, 29 May 2021 01:49:18 GMT

Hello
Please have a look here https://communities.sas.com/t5/SAS-Programming/Convert-XPT-to-SAS-Datasets/td-p/97872
There is an elegant solution relevant to your needs in the post by @Tom .

Re: convert xpt files to sas datasets

Ksharp — Sat, 29 May 2021 11:19:52 GMT


/* 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文件\XPT  ;


/*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: convert xpt files to sas datasets

lc7033907 — Sun, 30 May 2021 03:04:30 GMT

@Sajid01 Thanks. It's really helpful.

Re: convert xpt files to sas datasets

lc7033907 — Sun, 30 May 2021 03:13:29 GMT

@Ksharp I understand your ideas. But I found your path_in and path_out are exactly the same, so I get a little bit confused. As I stated libraries for xpt files and sas datasets that I want to save, should I put

%let path_in=C:\statdata\onco\datasets\sdtmdata\validation\xpt_new;

%let path_out=C:\statdata\onco\datasets\sdtmdata\output;

Thanks.

Re: convert xpt files to sas datasets

Ksharp — Sun, 30 May 2021 09:42:01 GMT

Sure. No problem. if you want store sas dataset into another directory .

Re: convert xpt files to sas datasets

lc7033907 — Sun, 30 May 2021 21:45:33 GMT

@Ksharp thanks. I understand now.

Re: convert xpt files to sas datasets

purpleclothlady — Thu, 13 Oct 2022 16:17:16 GMT

Hi:

I tested because I also need to convert multiple xpt files to sas datasets. it works perfectly. thanks @Ksharp

/******************************************************************************************
     READ MULTIPLE XPT FILE IN ONE DIRECTORY AND SAVE INTO EXCEL FILES
*****************************************************************************************/					
/* PATH TO ADDRESS XPT FILE-ALL XPT FILES ARE STORE IN XPTFILE(---ONLY XPT FILES IN THIS DESIGNATED FOLDER---) */
%let path_in= C:\xptfile;
/* PATH TO STORE SAS DATASET-NEED CREATE THE FOLDER-OUTFOLDER */
%let path_out= C:\outfolder;

/*TRANSFORM MUL XPT FILES INTO A SAS DATAASETS*/
/*CREATE A LIBNAME FOR OUTPUT FOLDER SAS DATASET*/
libname out v9 "&path_out";

data _null_;
/*OPEN EACH XPT FILE AND GET THE TOTAL NUMBER OF XPT FILES -&DID*/
		rc=filename('x',"&path_in");
		did=dopen('x');

/*USE CALL EXECUTE  AND DO LOOP TO READ IN XPT FILES */
		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: convert xpt files to sas datasets

NP2212 — Tue, 21 Feb 2023 21:07:13 GMT

I am trying to use this solution. But I am finding below error in do loop. Can you help?

3280 do i=1 to dnum(did);
3281 memname=dread(did,i);
3282 call execute(cat("libname tranfile xport
3282! '&path_in\",strip(memname),"';proc copy in=tranfile out=out ;run;"));
3283 end;
3284
3285 run;

NOTE: Argument 1 to function DNUM(0) at line 3280 column 19 is invalid.
ERROR: Invalid DO loop control information, either the INITIAL or TO
expression is missing or the BY expression is missing, zero, or
invalid.
rc=0 did=0 i=1 memname= _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following
places. The results of the operations have been set to missing
values.

Re: convert xpt files to sas datasets

Tom — Tue, 21 Feb 2023 22:02:01 GMT

You are not showing the actual cause of the error. The error is saying that using a directory handle of zero is invalid with the DNUM() function.

Assuming that you created the DID variable by using the DOPEN() statement that means that the open failed. So the path you tried to read does not exist, or is not actually a directory, or perhaps it is a directory but you do not have permission to read it.

Re: convert xpt files to sas datasets

Ksharp — Wed, 22 Feb 2023 11:49:34 GMT

As @Tom pointed out. either the fold you stored xpt file does not exist ,or you don't have permission to enter it.