topic Re: Convert SAS transport file with file name containing space/hyphen into SAS in SAS Procedures

Convert SAS transport file with file name containing space/hyphen into SAS

deleted_user — Sun, 06 Dec 2009 01:37:05 GMT

Hello,

I have one question to convert SAS transport file into SAS datasets. The SAS transport files are exported from EDC(Ommicom) systme, their names contain space and/or hyphen. I tried to import the transport file into SAS using proc copy. it won't let me do so. The errors said that the SAS file name is not valid. I tried to rename the transport file name, but it did not work.

What happened is that transport file names are the first 8 characters of the form names in the EDC. It would take us several month to rename the forms, update the edit programming and validation.

I am very appreciated if anyone has any ideas to share with me at yao_peter2002@yahoo.com.

Thank you so much!
Peter

Re: Convert SAS transport file with file name containing space/hyphen into SAS

sbb — Sun, 06 Dec 2009 16:42:49 GMT

Peter, help out by explaining more about an EDC(Ommicom) system and how it can generate a SAS transport file? Also, be careful when using the word transport because the SAS system has a transport-format (XPORT) file structure, likely not what you are referring to, I would say.

An Internet search on search argument strings "edc", "ommiom" (and also "omnicom possibly), did not yield any useful or revealing information about your challenge.

If you have created an "export" file and you are looking to import that data into SAS, possibly using PROC IMPORT, possibly there you are discussing your challenge with having a "blank" in either the file-name itself or possibly a column name within a file.

Suggest you clarify what you are attempting to do - import external data into SAS or something else, and maybe also share any SAS error diagnostic logs (COPY and PASTE directly from your SAS session into a forum post/reply). Also, information about your SAS version, OS environment, and whether SAS is executing local/report from your computer system -- these pieces of information are also very useful for constructive feedback.

Scott Barry
SBBWorks, Inc.

SAS/ACCESS Interface to PC Files: Reference, Supported Data Sources and Environments
http://support.sas.com/documentation/cdl/en/acpcref/61891/HTML/default/a003094743.htm

Re: Convert SAS transport file with file name containing space/hyphen into SAS

Peter_C — Sun, 06 Dec 2009 23:35:38 GMT

PeterY
if the name with blanks is "peter y" try
libname retry xport 'your transport format xport library' ;
data work.inSAS ;
set retry."peter y"n ;
run ;

good luck
PeterC

Re: Convert SAS transport file with file name containing space/hyphen into SAS

deleted_user — Mon, 07 Dec 2009 02:02:17 GMT

Scott,

Thanks for your reply. EDC is eletronic Data capture system for Clinical trials. The One we are using is one developed by Ommicomm. Its web site is at http://www.omnicomm.com/1Home/index.asp.
basically, there are three applications, TrialBuilder is one which is used to develop the Application. Trial Explorer is used to publish the application developed using TrialBuilder. TrailMaster is a web-based application to collect the data on the line. In TrialMaster, there is one function to export data into various formats such as SAS transport files (XPT extension).

What I tried to do is to import an transport file (.XPT) exported from TrialMaster into a SAS dataset. What happend is that the name of transport files are the first 8 characters of the forms in Trial Builder. In some cases, there is space in it. That prevents me from importing. I used proc copy. Somehow, proc cimport does not work.

SAS version is 9.1.3, OS is Window XP. SAS is running from my local machine.

Thanks!
Peter

Re: Convert SAS transport file with file name containing space/hyphen into SAS

deleted_user — Mon, 07 Dec 2009 02:12:07 GMT

PeterC,

Thanks for your code, but it does not work.

The following are the codes and log file. Any further ideas would be appreciated,

Thanks!
PeterY

libname trans xport 'C:\project\temp\peter\edc\ODI 1_0.xpt';
proc contents data=trans._all_;
run;
data work.name1;
set trans."ODI 1_0"n;
run;

log file is

28 data work.name1;
29 set trans."ODI 1_0"n;
ERROR: The value ODI 1_0 is not a valid SAS name.
30 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.NAME1 may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.NAME1 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: Convert SAS transport file with file name containing space/hyphen into SAS

sbb — Mon, 07 Dec 2009 03:15:00 GMT

So, suggest you try changing the file-name to specify an underscore instead of a blank. I don't expect you will be able to use these files as-is with the imbedded blank in the name.

Scott Barry
SBBWorks, Inc.

Re: Convert SAS transport file with file name containing space/hyphen into SAS

Flip — Mon, 07 Dec 2009 13:29:04 GMT

Have you contacted Ommicomm to find out why their software is generating invalid files?

Re: Convert SAS transport file with file name containing space/hyphen into SAS

deleted_user — Mon, 07 Dec 2009 15:46:32 GMT

SAS transport file can be renamed by removing any spaces, but the underneath sas name still keeps the previous name. Thanks for the suggestion though.

Re: Convert SAS transport file with file name containing space/hyphen into SAS

deleted_user — Mon, 07 Dec 2009 15:50:00 GMT

Yes, I did.

What happened is that we left SAS name as blank on each form, Then exported SAS transported file takes first 8 charcters of the form caption. In some cases, it contains space or hyphen. I am adding the name for each form. But it takes us to redo the validation. I am learning new things since this is the first EDC system we are doing,

Thanks!
Peter

Re: Convert SAS transport file with file name containing space/hyphen into SAS

sbb — Mon, 07 Dec 2009 16:00:50 GMT

As was mentioned by Flip, you would be best served by contacting the "source" of the utility used to export your EDC data, specifically with interest on how the blank-character condition is addressed.

Scott Barry
SBBWorks, Inc.

Re: Convert SAS transport file with file name containing space/hyphen into SAS

deleted_user — Mon, 07 Dec 2009 18:18:51 GMT

Yes, we found the cause, and are adding the SAS names for each form. The problem will be taken care of. But it takes us days of work for the validation. I am interested if the conversion to SAS is possible in case the file name contains the space.

Re: Convert SAS transport file with file name containing space/hyphen into SAS

Peter_C — Mon, 07 Dec 2009 18:22:14 GMT

since the xport file format is published, you should be able to define where the name is specified internally and replace invalid characters with underscores. since you never put anything into that anyway, it might be ok to set it to a constant like PETERYAO. Keep the original to store an the dataset label of the SAS9 converted form.
Just open the xpt file with a binary file viewer to see the internals.
good luck
PeterC Message was edited by: Peter.C

Re: Convert SAS transport file with file name containing space/hyphen into SAS

sbb — Mon, 07 Dec 2009 20:23:20 GMT

You should be able to answer your last question by testing the logic yourself - as previously suggested, change the file name and observe the SAS system's behavior as compared to before changing the name. Sounds straightforward to get a reasonable answer, especially since you have not found other replies who have personal experience with EDC and Omnicom.

Scott Barry
SBBWorks, Inc.

Re: Convert SAS transport file with file name containing space/hyphen into SAS

Peter_C — Mon, 07 Dec 2009 22:38:08 GMT

I asked no questions, Scott !
but with a sas platform on which to test, (rather than a sony phone) it is easy to demo the method.
On my windows vista platform it is easy to demo that at position 409, the binary export format data set holds the memName part of the two part data set name.
I'm trialling a single data set export library.[pre]libname xportf xport '.\test1.xpt' ;
proc copy in=sashelp out= xportf noclone ;
select shoes ;
run ;
libname xportf clear ;

filename xportf '.\test1.xpt' lrecl=1024 recfm=f ;
* the next line will display the binary (on display manager);
proc fslist file= xportf ; run;

* now collect the memName and replace it ;
data _null_ ;
infile xportf sharebufferS ;
file xportf ;
input @409 memName $char8. @ ;
put @409 'PETERYAO' @ ;
PUT ;
call symputx( 'orig_name', memname );
STOP ;
RUN ;
%put orig_name=(&orig_name);
filename xportf clear ;
libname xportf xport '.\test1.xpt' ;
option validvarname= any ;
PROC contents data= XPORTf._all_ ;
RUN ;[/pre]

That seemed to work for me.
I would hope the platform independant nature of the export format will mean that this demo will work for you too.

PeterC

Re: Convert SAS transport file with file name containing space/hyphen into SAS

sbb — Mon, 07 Dec 2009 23:14:22 GMT

To Peter.C -- apology please. My reply was intended for the OP, not yourself. Sorry for the confusion it caused.

Scott Barry
SBBWorks, Inc.

Re: Convert SAS transport file with file name containing space/hyphen into SAS

Peter_C — Tue, 08 Dec 2009 00:00:48 GMT

no worry, Scott
no apology required ... I just wanted clarity ..
and to clarify the idea of "fixing" the export format file interrnals created by that (foolish(?) application which did not validate the table name before using it in the export format file (it is so easy to validate/default a SAS5 standard data name ... imho)
I was also able to use the method to generate a table with an invalid name and observe SAS92 behaviour ... just ignoring the table.
Does it work for you?
regards
PeterC

Re: Convert SAS transport file with file name containing space/hyphen into SAS

Peter_C — Wed, 09 Dec 2009 12:15:49 GMT

for the definition of xport format file: internals including the data set name position, see http://support.sas.com/techsup/technote/ts140.html . Sorry it took me so long to come up with the reference. 😉
PeterC