How to I merge data on a remote server with data on my local computer?

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

How to I merge data on a remote server with data on my local computer?

I'm working in SAS on a remote server and am having a little trouble trying to merge data that's on the remote server with data that I have on my local computer.  I had assumed that once I submitted code locally to import my data from my computer into a "work." file, it would be as simple as remote submitting the rest of the code and calling up my "work." dataset in the SQL JOIN statement.  Unfortunately, it doesn't work like that.  When I refer to it in a rsubmit code, I end up getting a message that my locally derived "work." file does not exist (File WORK.CODEIMPORT_HS_LS.DATA does not exist).

HERE IS SOME EXAMPLE CODE TO ILLUSTRATE PROBLEM AS SIMPLY AS POSSIBLE:

ASSUME THAT I LOCALLY SUBMIT THE FOLLOWING CODE:

          %let server=*REMOTESERVER*;

          options comamid=tcp remote=server;

          signon username=_prompt_;

          *CREATING CODE LISTS THAT PULL FROM TABULATED EXCEL FILE*;

          LIBNAME codes pcfiles path="C:\Users\Mitch\Documents\sterilization\CohortCodeImport\StudyCodes_103013.xlsx";

               DATA work.codeimport_HS_LS;

                 SET codes.'HS_LS$'n;

                 HS_LS=1;

               RUN;

          PROC PRINT DATA=work.codeimport_HS_LS;

               RUN;

          LIBNAME codes CLEAR;

          PROC CONTENTS DATA=work.codeimport_HS_LS;

          RUN;

EVERYTHING RUN'S FINE.  BUT NOW ASSUME I RSUBMIT THE FOLLOWING CODE:

          RSUBMIT;

               PROC CONTENTS DATA=work.codeimport_HS_LS;

               RUN  ;

          ENDRSUBMIT;


I get an alert in my log that work.codeimport_HS_LS doesn't exist.  Am I missing something here?  Is there any way to get these two to interact or do I have to do everything on the server (I don't necessarily have to keep my excel file with my source codes on my local computer but I'd like to).


Thanks for everyone's time and assistance!

HERE IS THE ACTUAL CODE I'M USING, IN CASE IT HELPS:

%let server=*REMOTESERVER*;

options comamid=tcp remote=server;

signon username=_prompt_;

*CREATING CODE LISTS THAT PULL FROM TABULATED EXCEL FILE*;

LIBNAME codes pcfiles path="C:\Users\Mitch\Documents\sterilization\CohortCodeImport\StudyCodes_103013.xlsx";

     DATA work.codeimport_HS_LS;

       SET codes.'HS_LS$'n;

       HS_LS=1;

     RUN;

     PROC PRINT DATA=work.codeimport_HS_LS;

     RUN;

     LIBNAME codes CLEAR;

     PROC CONTENTS DATA=work.codeimport_HS_LS;

     RUN;

****************************************************************************************************************************************

/* Macro: STERILchainO -- subset outpatient claims for sterilization procedures then chain them together from the sequential yearly files,

  Parameters:

  yr_start: first year of data

  yr_end: last year of data

  output: name of file to output

*/

*Macro: sas program that writes a program:  This macro puts together outpatient claims from different years*;

%macro STERILchainO(yr_start,yr_end,output);

*subset claims to sterilization procedures*;

  %do yr=&yr_start %to &yr_end;

  proc sql;

  create table work.cohortO&yr as

  select a.enrolid, a.svcdate, a.sex, a.age, a.provid, a.dx1, a.dx2, a.proc1, a.svcdate, a.stdplac, b.CPT, b.description, b.HS_LS

  from raw.outptserv&yr */THIS IS ON THE REMOTE SERVER/* as a INNER JOIN work.codeimport_HS_LS */THIS IS ON MY LOCAL COMPUTER/* as b

  ON (a.proc1=b.CPT)

  ORDER BY enrolid, svcdate;

  quit;

%end;

*merge all claims into file;

  data &output;

  set

  %do yr=&yr_start %to &yr_end;

  cohortO&yr

  %end;

  ;

  run;

  proc sort data=&output;;

  by  enrolid svcdate;

  run;

%mend;

options mprint;

%STERILchainO(2005,2011,HSLS_Cohort_O);


Accepted Solutions
Solution
‎11-06-2013 01:56 PM
Super User
Posts: 3,254

Re: How to I merge data on a remote server with data on my local computer?

Referring to your example code try this:

RSUBMIT;

               proc upload data = work.codeimport_HS_LS;

               run;

               PROC CONTENTS DATA=work.codeimport_HS_LS;

               RUN  ;

ENDRSUBMIT;

The UPLOAD copies the WORK dataset to the server where you run the CONTENTS procedure.

View solution in original post


All Replies
Solution
‎11-06-2013 01:56 PM
Super User
Posts: 3,254

Re: How to I merge data on a remote server with data on my local computer?

Referring to your example code try this:

RSUBMIT;

               proc upload data = work.codeimport_HS_LS;

               run;

               PROC CONTENTS DATA=work.codeimport_HS_LS;

               RUN  ;

ENDRSUBMIT;

The UPLOAD copies the WORK dataset to the server where you run the CONTENTS procedure.

Contributor
Posts: 50

Re: How to I merge data on a remote server with data on my local computer?

Thanks so much.  Worked perfectly. 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 608 views
  • 0 likes
  • 2 in conversation