topic Re: Appending in proc download? in SAS Procedures

Appending in proc download?

SAStaffan — Mon, 09 Jan 2017 13:19:21 GMT

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

Re: Appending in proc download?

RW9 — Mon, 09 Jan 2017 13:27:29 GMT

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.

Re: Appending in proc download?

Shmuel — Mon, 09 Jan 2017 13:37:17 GMT

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;

Re: Appending in proc download?

LinusH — Mon, 09 Jan 2017 13:38:53 GMT

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#n0saopgusahpu0n10w0lgz9t287x.htm

Re: Appending in proc download?

SAStaffan — Mon, 09 Jan 2017 14:13:41 GMT

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

Re: Appending in proc download?

Shmuel — Mon, 09 Jan 2017 14:25:26 GMT

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.

Re: Appending in proc download?

SAStaffan — Mon, 09 Jan 2017 14:32:44 GMT

Yes, thank you!

I was hoping I would not have to signoff, but it seems impossible =/

Re: Appending in proc download?

Shmuel — Mon, 09 Jan 2017 14:54:27 GMT

I don't think that signoff is a must.
just try it.

Re: Appending in proc download?

LinusH — Mon, 09 Jan 2017 15:06:33 GMT

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

Re: Appending in proc download?

Daniel-Santos — Mon, 09 Jan 2017 17:41:40 GMT

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

Re: Appending in proc download?

SAStaffan — Tue, 10 Jan 2017 14:35:23 GMT

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.

Re: Appending in proc download?

RichMorris — Wed, 25 Jan 2017 15:03:37 GMT

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.)