Help using Base SAS procedures

Appending in proc download?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Appending in proc download?

Hi!

 

I'm doing a proc download and it simply looks like this:

 

proc download data=my_server_lib.my_server_name

out=my_local_lib.my_local_name;

run;

 

This creates the data-set my_local_name at my_local_lib. Is there a way to make the download procedure APPEND the dataset to an already excisting dataset at my_local_lib?

 

If not, is there another way around it? I want to stay signon and appending a server-set to a local set.

 

Thank you!

 

//Staffan


Accepted Solutions
Solution
‎01-15-2017 03:28 PM
Super User
Posts: 5,441

Re: Appending in proc download?

What I think the OP is asking for is a way to avoid the local temporary storage step, and insert the data "on the fly" during proc download.

The short answer to that is no.

On could wish for an efficient ay to collect remote SAS data like it was a data from an external RDBMS via a SAS/ACCESS libref.

In SAS/CONNECT, there is Remote library Services, but last I checked, it is a performance killer. I guess it was mainly developed for data entry and SAS/SHARE, and displaying remote sata set meta data.

 

Another options is to use RSPT (Remote SQL Pass Through i guess...?), where you could do SQL insert from a remote SAS server data set (not tested myself though):

http://support.sas.com/documentation/cdl/en/shrref/69819/HTML/default/viewer.htm#n0saopgusahpu0n10w0...

Data never sleeps

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: Appending in proc download?

Posted in reply to SAStaffan

Just have a proc append after download.

 

proc download data=my_server_lib.my_server_name out=tmp;
run;

proc append base=my_local_lib.my_local_name data=tmp;
run;

Note that data structure would need to be the same.

Solution
‎01-15-2017 03:28 PM
Super User
Posts: 5,441

Re: Appending in proc download?

What I think the OP is asking for is a way to avoid the local temporary storage step, and insert the data "on the fly" during proc download.

The short answer to that is no.

On could wish for an efficient ay to collect remote SAS data like it was a data from an external RDBMS via a SAS/ACCESS libref.

In SAS/CONNECT, there is Remote library Services, but last I checked, it is a performance killer. I guess it was mainly developed for data entry and SAS/SHARE, and displaying remote sata set meta data.

 

Another options is to use RSPT (Remote SQL Pass Through i guess...?), where you could do SQL insert from a remote SAS server data set (not tested myself though):

http://support.sas.com/documentation/cdl/en/shrref/69819/HTML/default/viewer.htm#n0saopgusahpu0n10w0...

Data never sleeps
Trusted Advisor
Posts: 1,586

Re: Appending in proc download?

Posted in reply to SAStaffan

It seems to me that you can't append with proc download.

 

Full documentation can be found in next link:

http://support.sas.com/documentation/cdl/en/connref/61908/HTML/default/viewer.htm#prcd.htm

 

but you always can download to a work library and append the temporary dataset to the old one:

 

proc download data=my_server_lib.my_server_name

out=WORK.my_local_name;

run;

proc append   base=my_local_lib.my_local_name  data=WORK.my_local_name; run;

New Contributor
Posts: 4

Re: Appending in proc download?

Posted in reply to SAStaffan

Thank you all!

 

Yes, LinusH is right there. I would prefer not having to go via a local tmp-dataset, but that's not a possible way then. I'll consider other options then, as you suggest. 

 

But I'm still a bit confused. To make the proc append work, I need to signoff first?! From Shmuel:s example, the

 

proc download...

 out=work.my_local_name

run;

 

work refers to the local work on the computer, while the

 

proc append...

 data=work.my_local_name

run;

 

still refers to the server-work if I'm signon. Therefore I need to signoff first, then do the proc append?!

 

//Staffan

Trusted Advisor
Posts: 1,586

Re: Appending in proc download?

Posted in reply to SAStaffan

proc download copies the dataset from remote host to local host.

in order to run it you sign on to the remote host from your local.

 

the input is defined by data= and it should exist on the remote host.

the output is defined by out= and it will be created on the local host

 

you can signoff after the proc download to exceute the proc append.

 

I hope it is more clear now.

New Contributor
Posts: 4

Re: Appending in proc download?

Yes, thank you!

I was hoping I would not have to signoff, but it seems impossible =/
Trusted Advisor
Posts: 1,586

Re: Appending in proc download?

Posted in reply to SAStaffan
I don't think that signoff is a must.
just try it.
Super User
Posts: 5,441

Re: Appending in proc download?

Posted in reply to SAStaffan

But you need an endrsubmit; unless you are performing this interactively and with rsubmit command..

Data never sleeps
Contributor
Posts: 24

Re: Appending in proc download?

Hi.

 

Cannot be done with download as previously said.

 

You could use proc append/data step for that accessing throught a remote library.

 

SAS will implicitly create a temporary table and append that to your target table, but it will be transparent for you.

 

Assuming you have a valid signon session previously established:

 

* assign remote library;

libname rlib server=<my_server_name> slibref=<my_server_lib>;

 

* append remote to local table;

proc append base=llib.local_tab data=rlib.remote_tab force;
run;

 

This is the same as:

 

rsubmit;

* download remote table;

proc download data=<my_server_lib>.remote_tab

              out=llib.remote_tab;

run;

endrsubmit;

 

* append to local table;

proc append base=llib.local_tab

            data=llib.remote_tab force;
run;

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

 

New Contributor
Posts: 4

Re: Appending in proc download?

Posted in reply to SAStaffan

Yes, I was confusing "signoff" with "endrsubmit". I dont need to "signoff" to get the proc append to work.

 

There can be authorization problems between server and local libraries, so I was hoping to make the download-append in "one move". Mainly to make the code compact.

New User
Posts: 1

Re: Appending in proc download?

Posted in reply to SAStaffan

If the goal is to aggregate multiple small sets into a larger set on the server, you could define a view that references the base data set and  the data set(s) that you want to append. 

 

You should also be able to write a macro that identifies all of the sets in the libref, and have the macro build the view for you.

 

(Kind of klugey, I know.)

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 294 views
  • 1 like
  • 6 in conversation