How do I merge two files?

[Friddelin]

Hi, I'm new to SAS. I've received two files (.sas7bdat) from my supervisor, who has SAS professional edition or whatever it is called. It is installed directly on his computer. I'm working in SAS Studio University Edition Release 3.8 trough Google Chrome version 77. I'm having trouble using his files. I'm trying to merge them but it is not working. At first, I thought it was a language barrier since I was working on a Mac so I tried doing the same thing on a PC but I got the same error message. The code I'm using is:

/*creating a permanent library*/
libname T10LIB '/folders/myfolders/T10';

/*copy of the two files to merge*/
/*copy of the first file*/
data bas;
set T10LIB.bas_01;
/*copy of the second file*/
data randsf;
set T10LIB.randsf_01;

/*merge files*/
proc sort data=bas;
by Lop_nr_Sorsele;
proc sort data=randsf;
by Lop_nr_Sorsele;
data bas_00;
merge bas randsf;
by Lop_nr_Sorsele;
run;

The error message that I'm getting is: Some character data was lost during transcoding in the dataset T10LIB.BAS_01. Either the data contains characters that are not representable in the new encoding or truncation occurred during transcoding.

If I'm adding the code (encoding=any) after the filename bas_01 it is working. I'm getting the file that I want. However, the file does not work when I try to run it in the statistic module for example Summary Statistics. Regardless of which variables I'm choosing the result is the same, i.e. several error messages all saying either Invalid characters were present in the data or An error occurred while processing text data. When I'm testing with the original files it is working.

To the question: What I'm doing wrong? How do I produce a merged file that will work in the statistical module?

Hoping you experts have the answer to my frustration. Kind regards, Frida Lindberg

[Patrick]

"At first, I thought it was a language barrier since I was working on a Mac"

That's only the client side. The relevant bit here is the server side where SAS executes. With a local SAS installation (your supervisor) the client and the server will be on the same machine, with SAS University Edition these are two different machines (and it doesn't matter if the server is a virtual machine on the same hardware - it's a still different machine).

Your supervisor with a local SAS installation is almost certainly working fully in a Windows environment. The SAS University Edition (server side) runs under Linux as far as I know.

Ideally your supervisor provides you the data not just as Windows encoded .sas7bdat files but in a transport format (created using Proc Cport) which you then import into your SAS Linux environment (using Proc Cimport).

If you can't get the data in a transport format: Reading the documentation encoding=any actually just reads the data binary without doing any transcoding. That would then explain the errors you're getting. Not sure what exactly you need to do but it must be something as documented here.

And last but not least: Please terminate every single Data Step and Proc Step with a RUN; (or QUIT; for some procedures).

At first, I thought it was a language barrier since I was working on a Mac

[Friddelin]

Hi Patrick, thank you for a quick response. If I get the data in a transport format how do I use Proc Cimport? I will check out the link about transcoding that you posted. Trying to work from two angles, I've been stuck on this issue for two weeks now.

[Patrick · Accepted solution]

---

@Friddelin wrote:

Hi Patrick, thank you for a quick response. If I get the data in a transport format how do I use Proc Cimport? I will check out the link about transcoding that you posted. Trying to work from two angles, I've been stuck on this issue for two weeks now.

---

In case what @Tom proposes doesn't resolve your problem here the link to the docu for Proc CIMPORT. It requires your professor to provide you with export files created using Proc CPORT.

....and I'm calling it "your professor" as the SAS UE Edition is for non-commercial use only.

[Tom]

If you are lucky the issue is just that second option.  SAS UE should be running using UTF-8 encoding.  Your professors data must include some characters that are not part of the normal 7-bit ASCII codes.  Such as letters with accents, line-drawing characters, etc.  Those will take more than one byte in UTF-8.  So a 7 "character" string might take more than 7 bytes to store.

Try using the CVP engine. https://go.documentation.sas.com/?docsetId=nlsref&docsetTarget=p00fhxufzmc274n19xax2hllvin4.htm&docs...

libname T10LIB  cvp '/folders/myfolders/T10';

data bas;
  set T10LIB.bas_01;
run;

[Friddelin]

Thank you Tom for providing correct code. I get the file "bas". No error message and it looks just like the file bas_01 with the same amount of rows and columns. However, I got the following message:

NOTE: Data file T10LIB.BAS_01.DATA is in a format that is native to another host, or the file encoding does not match the session

encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce

performance.

I haven't tried to merge the files yet but I ran the new file "bas" that I got from your code trough the statistical module (Summary Statistics) and it worked! No error messages! However I got the following message:

NOTE: ODS statements in the SAS Studio environment may disable some output features.

Question: How do I interpret the notes? Am I loosing data on the way?

Update: I merged the files successfully using the following code:

libname T10LIB  cvp '/folders/myfolders/T10';

data bas;
set T10LIB.bas_01;
run;

/*copy of the two files to merge*/
data bas_02;
set WORK.bas;
run;
/*copy of the second file*/
data randsf;
set T10LIB.randsf_01;
run;

/*merge files*/
proc sort data=bas_02;
by Lop_nr_Sorsele;
run;
proc sort data=randsf;
by Lop_nr_Sorsele;
run;
data bas_00;
merge bas randsf;
by Lop_nr_Sorsele;
run;

However, I still get the NOTES stated above (that bas_01 is in a format that is native to another host), and the same when I ran the new merged file rough the statistical module (Summary Statistics) (about ODS statements may disable some output features).

[andreas_lds]

You will get the note as long as you don't convert the datasets in T10LB. The note just tells you that sas converted the dataset on the fly and if there are not warnings about data-loss, the only thing you will experience is an increased runtime. Nothing to worry about as long as the datasets aren't to big.

[Friddelin]

Okay, thanks Andreas! I don't care about the runtime. However, if I'm getting a warning about data-loss when using another statistical module, how do I convert the datasets in the library (T10LIB)?