Merging files in SAS WRDS Cloud

[AnaVil]

Dear Community,

I am a new SAS Studio user (9.4) and am struggling with the following task.

I need to merge two files. One is located in SAS WRDS Cloud (let's name it File 1), and another one is located on my local drive (let's name it File 2). File 1 is approximately 300 GB, so all manipulations must be performed in the Cloud, and then the final version of the merged file would be stored locally. 

I need to merge File 1 and File 2 based on CompanyID and Year. When I try to sort File 1 by CompanyId and Year prior to merging, the system rightfully says that I do not have permission to manipulate that file (as it is a WRDS raw data file, the message is "ERROR: User does not have appropriate authorization level for library RBDX). However, I cannot download the file locally as it is too large. 

What is the best way to merge the two files, and what would be the appropriate code?

Many thanks for any help!

Best,

Ana.

[Cynthia_sas · Accepted solution]

Hi:

  WRDS is supported by the Wharton help desk. So you'll need to find out from them where you can upload your local data file. My guess is that SAS on the WRDS server cannot read your local file and so you'll need to upload your local file to a location on the server. You may need to get help from the Wharton help desk to find out how to do this.

  Next, even if you do not have write access to sort FILE1 and write it back to the permanent location, my guess is that you can sort your FILE1 and use the OUT= option in PROC SORT to write the data to the WORK library. Then once both of your files are in the WORK library, you should be able to do a merge.

  Conceptually, it would be like using SASHELP.CLASS and SASHELP.CLASSFIT. SASHELP is a read-only folder on the SAS OnDemand server, so although I can sort both of these files, I cannot write the sorted copy back to SASHELP because I do not have authorization to write to SASHELP. So I have to do something like this:

** when you do not have write access to a file;
** you can sort the file and write a sorted copy into WORK;
** and use the copy that's in work for any subsequent steps;
proc sort data=sashelp.class out=work.class;
by name age;
run;

** instead of using SASHELP.CLASSFIT, you will need to upload;
** your local file to the server for sorting and merging;
proc sort data=sashelp.classfit out=work.classfit;
by name age;
run;

data inboth;
  merge work.class(in=inclass) 
        work.classfit(in=infit);
  by name age;
  if inclass=1 and infit=1 then output inboth;
run;

proc print data=inboth;
title 'After Merge';
run;
title;

  In this example, SASHELP.CLASS would be the FILE1 that is already on the WRDS server. You should be able to SORT the file as you need as long as you use the OUT= option and write the sorted copy of the data to a location like WORK where you have write access. Then in this example, SASHELP.CLASSFIT would be the FILE2 on your local machine. You'll probably need to upload your data file from your local machine to the WRDS server. If your File2 is a raw data file, then after you do the upload, you'll need to read File2 into SAS format prior to your sort. But then, you should be able to sort File2 as you need and then do the merge.

  Hope this points you toward a solution.

Cynthia