Desktop productivity for business analysts and programmers

Referring eg table to transfer files

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 338
Accepted Solution

Referring eg table to transfer files

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


Accepted Solutions
Solution
‎01-08-2014 05:05 PM
Super User
Super User
Posts: 6,845

Re: Referring eg table to transfer files

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;

View solution in original post


All Replies
Community Manager
Posts: 2,889

Re: Referring eg table to transfer files

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

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

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 Smiley Happy...and i cannot change it :-)

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

Thanks Again Chris...

Super User
Super User
Posts: 6,845

Re: Referring eg table to transfer files

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?

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

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!

Solution
‎01-08-2014 05:05 PM
Super User
Super User
Posts: 6,845

Re: Referring eg table to transfer files

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;

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

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!

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

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!

Super User
Super User
Posts: 6,845

Re: Referring eg table to transfer files

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

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

Thanks a lot Tom!!!

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

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 ;

   /*"Statement is not valid or it is used out of proper order.*/

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

   /*"Statement is not valid or it is used out of proper order.*/

  length username $32 file $256 ;

   /*"Statement is not valid or it is used out of proper order.*/

  input ;

  /*"Statement is not valid or it is used out of proper order.*/

  file = _infile_;

  /*"Statement is not valid or it is used out of proper order.*/

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

run;

proc sort data=files;

  /*work.files,data doesn't exist.*/

  by username;

run;

Super User
Super User
Posts: 6,845

Re: Referring eg table to transfer files

Sounds like a case of missing semi-colon or miss-matched quotes.  It looks from the error message that SAS is not seeing the DATA statement and hence the INFILE and other statements are invalid outside of a data step.

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

yes Tom - one semicolon was missing. but now i am getting error message saying that "insufficient authorization to access pipe.

However, i have got your point so far in your code logic.

have quick question - can you please enter comment to explain two line i mention below from your code...really appreciate your time....!!!

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

  infile cmd pipe filevar=cmd ;

Thanks!

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

i cannot do following since i am using 9.1 and i think following task is not available in 9.1 - i think have to edit config file...

Expand Server Manager -> SASAPP - > SASAPP - Logical Workspace Server . Right click on SASAPP - Workspace Server. Go to Options Tab -> Advanced Options -> Launch Properties - > Check Allow XCMD.

Super Contributor
Super Contributor
Posts: 338

Re: Referring eg table to transfer files

Which file or files need to be updated in order to provide -allowxcmd command - i think have to do that...

Thanks!

🔒 This topic is solved and locked.

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

Discussion stats
  • 15 replies
  • 569 views
  • 0 likes
  • 3 in conversation