BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

15 REPLIES 15
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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?

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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!

Tom
Super User Tom
Super User

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;

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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!

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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!

Tom
Super User Tom
Super User

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

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

Thanks a lot Tom!!!

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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;

Tom
Super User Tom
Super User

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.

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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!

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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.

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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

Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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