topic Re: Referring eg table to transfer files in SAS Enterprise Guide

Referring eg table to transfer files

woo — Wed, 08 Jan 2014 18:11:35 GMT

Hi all - i need little help on coding...

- i am using 9.1 sas with eg 4.1.

- we have 4 different share drives from where we pull in user efforts as mdb files into SAS server - weekly

- all these mdb files stored on sas server at one place, let's say "f:\woo". all mdb files startd with particular "username_sales.mdb"

- we have one control EG table as sas dataset with users info. from all 4 share like "usename", "location" (here important variable is "username" and "location") and all...

- now we don't know how many mdb files will be pulled in into sas server every week but by referring/using EG table - we have to transfer those mdb files to all 4 different share drives according to the "username" and "location" variables.

- now i don't know how to embedding that EG table into sas code so that it could use "username" and or "location" from control EG tables and transfer mdb files to proper share drives.

Please help...

Re: Referring eg table to transfer files

ChrisHemedinger — Wed, 08 Jan 2014 18:43:42 GMT

There is a version of the Copy Files task that works with EG 4.1. It might help you to accomplish this.

See this blog post for details and download information:

There and back again: copying files in SAS Enterprise Guide - The SAS Dummy

Chris

Re: Referring eg table to transfer files

woo — Wed, 08 Jan 2014 19:17:01 GMT

thanks for sharing it Chris - love it. i read out almost all different issue on follow up.

But there are two things -

Firstly - i am not sure how this will work for me because i have multiple mdb files and wants to copy to different share drive according to the "username" and "location" variables from EG table/dataset...i don't know how i can use this to write some macro or data/proc steps code...

Secondly - "download copy file task" not allowed to install/download due to company policy - which is not your problem ...and i cannot change it 🙂

Let's see if someone can help me to build code...

Thanks Again Chris...

Re: Referring eg table to transfer files

Tom — Wed, 08 Jan 2014 19:34:43 GMT

What is the actual question here?

Sounds to me like you have single folder that contains files that match a pattern of <username>_<some other text>.mdb.

You want to read the names of those files, parse out the username, merge the list with a table that says where to copy files for that user and then generate code that will copy the files?

So if the source folder contains:

tom_db1.mdb

chris_db2.mdb

woo_db3.mdb

And the user table contains

USERNAME|PATH

tom|f:\home\tom

chris|g:\home\chris

woo|h:\home\woo

Then you want copy tom_db1.mdb from the source directory to the f:\home\tom directory.

Is that what you want to do?

Re: Referring eg table to transfer files

woo — Wed, 08 Jan 2014 20:06:03 GMT

Hi Tom - i think yes BUT,

/*we have "username" (Tom, Chris, Woo) and "location" (NY, OH, VA) variables in control table*/

/*we don't have path in control table for users */

/*for NY we have path //ny/shares*/ *let's say TOM;

/*for OH we have path //oh/shares*/ *for Chris;

/*for VA we have path //va/shares*/ *for Woo;

so we have to match username from control table and associated share drives that we mention in code...

also - we have like more than 10 mdb created for different share drives...

Thanks Tom!

Re: Referring eg table to transfer files

woo — Wed, 08 Jan 2014 21:03:09 GMT

i don't know if we can embed control EG table with path somehow OR logic would be,

if Tom_db1.mdb exist in "f:\woo" then copy f:\woo\Tom_db1.mdb to \\ny\shares

if Chris_db2.mdb exist in "f:\woo" then copy f:\woo\Chris_db2.mdb to \\oh\shares

if woo_db3.mdb exist in "f:\woo" then copy f:\woo\woo.mdb to \\va\shares

Thanks!

Re: Referring eg table to transfer files

Tom — Wed, 08 Jan 2014 22:05:04 GMT

I have no idea how Enterprise Guide makes this problem easier or harder. I am also not sure if you want to copy the actual MDB files or if you want to convert them into SAS datasets. Typically I would use SAS to generate the COPY command and then execute it. That might be a problem with EG as frequently the SAS process server has disabled the ability to shell out and run operating system commands.

Sounds like your control file just has the information needed to generate the target directory name. Something like this:

username location

tom ny

chris oh

woo va

%let source=F:\ ;

%let control_file=MASTER.USERS ;

data files ;

infile "dir /b &source\*.mdb" pipe ;

length username $32 file $256 ;

input ;

file = _infile_;

username = upcase(scan(file,1,'_'));

run;

proc sort data=files;

by username;

run;

data files ;

merge files (in=in1) &control_file;

by username ;

length dir cmd $256 ;

dir = cats('\\',location,'\shares');

cmd = catx(' ',"copy &source"||file,dir);

infile cmd pipe filevar=cmd ;

input ;

* Might put something here to capture any error messages from the operating system. ;

put _infile_;

run;

Re: Referring eg table to transfer files

woo — Thu, 09 Jan 2014 01:12:12 GMT

Thanks for your time Tom and sorry if you find question is not clear...BUT

i want to copy actual mdb files created in'f:\woo to different share drives where they belongs, like Tom_db1.mdb file goes to "\\ny\shares" and Chris_db2.mdb files goes to "\\oh\shares")...

/*i think we can avoid use of control table- but not sure*/

/*for me issue is we don't know how many mdb files will be created each wk but those mdb files creates according to the control table, whatever number of username are there - mdb files will be created - so lets say one wk - there are 20 mdb files created and for next week 45 mdb files will be created but all these mdb files need to be copied to appropriate share location/where it belongs to - and that is why i thought we can use control table to direct mdb files to appropriate location*/

if we don't use control table then i am thinking like,

/*define path according to "location" variable from control table*/

%let ny=\\ny\shares

%let oh=\\oh\shares

%let va=\\va\shares

/*then show something like that - just a thought */

if Tom_db1.mdb exist in folder "f:\woo" then copy "f:\woo\Tom_db1.mdb" to "&ny"

if Chris_db2.mdb exist in folder "f:\woo" then copy "f:\woo\Chris_db2.mdb" to "&oh"

Thanks Tom!

Re: Referring eg table to transfer files

Tom — Thu, 09 Jan 2014 01:52:06 GMT

Please read the code I posted. It will read the filenames into a dataset and use that dataset to generate the copy commands.

Another way to map usernames to locations is to use a format.

proc format ;

value $userloc "TOM"="\\ny\shares" "CHRIS"="\\oh\shares";

run;

So then instead of merging with the control dataset you can convert directly from username to target directory.

dir = put(username,$userloc.);

Re: Referring eg table to transfer files

woo — Fri, 10 Jan 2014 07:01:15 GMT

Thanks a lot Tom!!!

Re: Referring eg table to transfer files

woo — Tue, 14 Jan 2014 18:45:56 GMT

Hi Tom -

i have created same test scenario in my environment as you suggested above and run only following code for test but i am getting an error message saying that

"Statement is not valid or it is used out of proper order" at below ares.

%let source=F:\ ;

%let control_file=MASTER.USERS ;

data files ;