transferring files using (proc format+control table+ location)

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

transferring files using (proc format+control table+ location)

Hello friends - i have one issue with transferring files - some time back Tom helped me with very good code (Thanks Tom) to moving mdb files having in one directory and moving to different share drives using control table...BUT May be I am not able to understand it thoroughly BUT i would like to give whole scenario again in this cube if someone can help please.

/*I am going to use Proc format to accomplish this task as Tom suggested.*/

/*Background/*

/*i have one control table as below*/

work.control

Username    Location

USUser1     NY

USUser2     PA

USUser3     NY

USUser4     PA

/*I have some mdb files @ below locaiton.*/

f:\woo\db\

USUser1_market.mdb

USUser2_market.mdb

USUser3_market.mdb

USUser4_market.mdb

/*all above mdb files should goes to proper location either to NY folder or PA folder  - see path below*/

/*starting code*/

/*step1 - defining format where specific file need to move to */

proc format;

value location

                      "NY"="f:\transfer\mdb\ny"

                      "PA"="f:\mkt\files\pa";

run;

/*I need help from here please*/

/*have to sync control table + mdb files + user format to send files to appropriate location*/

Thanks!


Accepted Solutions
Solution
‎02-04-2014 05:18 PM
Super User
Super User
Posts: 7,046

Re: transferring files using (proc format+control table+ location)

You need to combine your dataset, your filelist and your format to get the information you need.

To get your filelist as a dataset you can use the DOS command DIR. While you are at it pull the username part out of the filename.

%let path=f:\woo\db ;

data files ;

   infile "dir /b &path\*.mdb" pipe truncover lrecl=256 ;

   input filename $256. ;

   username = scan(filename,1,'_');

run;

Now combine with your userlist (note you might merge both and also perhaps convert the usernames to all upper or lowercase to insure they match).

While you are at it you can use the location variable and the $LOCATION format to find the target directory.

data both ;

  merge files (in=in1) control (in=in2);

  by username ;

  if in1 ;

  targetdir = put(location,$location.);

run;

Now you have all of the information you need to issue a copy command.  Try just generating the copy command first to see if you can get the syntax correct.

data both ;

  merge files (in=in1) control (in=in2);

  by username ;

  if in1 ;

  targetdir = put(location,$location.);

  copycmd = catx(' ','copy', "&path\" || filename , targetdir);

run;

Once you have the command right then execute them.  If your filenames or directory names include spaces hen you might need to generate the copy commands with quotes around the filenames.

data _null_;

    set both ;

    infile cmd pipe filevar=copycmd ;

    *input @;

run;

Message was edited to eliminate the INPUT statement in the last step.

View solution in original post


All Replies
Solution
‎02-04-2014 05:18 PM
Super User
Super User
Posts: 7,046

Re: transferring files using (proc format+control table+ location)

You need to combine your dataset, your filelist and your format to get the information you need.

To get your filelist as a dataset you can use the DOS command DIR. While you are at it pull the username part out of the filename.

%let path=f:\woo\db ;

data files ;

   infile "dir /b &path\*.mdb" pipe truncover lrecl=256 ;

   input filename $256. ;

   username = scan(filename,1,'_');

run;

Now combine with your userlist (note you might merge both and also perhaps convert the usernames to all upper or lowercase to insure they match).

While you are at it you can use the location variable and the $LOCATION format to find the target directory.

data both ;

  merge files (in=in1) control (in=in2);

  by username ;

  if in1 ;

  targetdir = put(location,$location.);

run;

Now you have all of the information you need to issue a copy command.  Try just generating the copy command first to see if you can get the syntax correct.

data both ;

  merge files (in=in1) control (in=in2);

  by username ;

  if in1 ;

  targetdir = put(location,$location.);

  copycmd = catx(' ','copy', "&path\" || filename , targetdir);

run;

Once you have the command right then execute them.  If your filenames or directory names include spaces hen you might need to generate the copy commands with quotes around the filenames.

data _null_;

    set both ;

    infile cmd pipe filevar=copycmd ;

    *input @;

run;

Message was edited to eliminate the INPUT statement in the last step.

Super Contributor
Super Contributor
Posts: 358

Re: transferring files using (proc format+control table+ location)

/*This is awesome Tom*/

/*i have gone though step by step it's working awesome*/

- The only thing is somehow i am getting files getting transfer to one folder only i.e "NY". "PA" files not getting there - must be some typo for me -

- but your "copycmd" variable is created absolutely fine in "BOTH" dataset.

- "i m checking in"

Thanks a lot Tom!!!

Super User
Super User
Posts: 7,046

Re: transferring files using (proc format+control table+ location)

Eliminate the INPUT statement in the last data step and it should process all of the commands instead of just stopping after the first one.

Super Contributor
Super Contributor
Posts: 358

Re: transferring files using (proc format+control table+ location)

I have eliminated complete "INPUT" statement but still doing transfer for "NY" only - not for "PA".

- this is from log for last data step

Note: The variable copycmd exists on an input data set, but was also specified in an I/O statement option. The variable will not be included on any output data set.

Note: the infile cmd is:

          Unnamed pipe access device,

          process=copy,,,,,,,,,,,,,,,,,,,,,,,,,,,,

          recfm=v, lrec=256

         /*same thing for all files (bold log) */

Note: 0 records were read from infile CMD.

        /*same note: for all mdb files*/

Note: there are xxxxxx observation read from data set work. both /*we have all observation we need here*/

- Thanks for your time!!!

Super User
Super User
Posts: 7,046

Re: transferring files using (proc format+control table+ location)

Sounds like either a typo in the path or an access issue.  For example when I try to run this impossible copy command I get an error message from DOS.

copy c:\temp\testfile.dat c:\nosuchdirectory\testfile.dat

The system cannot find the path specified.

To see if DOS is generating any messages when running the commands you can put the INPUT statement back, but you need to protect from reading past the input stream by checking if the input stream has reached the end before issuing the INPUT statement.  Use this data step:

data _null_;

  set cmds;

  infile cmd pipe filevar=cmd end=eof;

  do while (not eof);

    input;

    put _infile_;

  end;

run;

Might be easier to debug if you just tried to execute one of the commands to copy one of the NY files.

Super Contributor
Super Contributor
Posts: 358

Re: transferring files using (proc format+control table+ location)

Hi Tom - i did try to use simple copy command using noxwait option but surprisingly it's doing nothing. I have tried below code in 3 different server but no transfer | no error message | no sign if code is working or not...

Moreover - if i run your complete code then it's doing transfer in one folder...

Amazing!!! No idea what is going on ....

but i will check with your new data _null_;

data _null_;

  set cmds;

  infile cmd pipe filevar=cmd end=eof;

  do while (not eof);

    input;

    put _infile_;

  end;

run;

Thanks a lot for staying with this mess....!!!

Super Contributor
Super Contributor
Posts: 358

Re: transferring files using (proc format+control table+ location)

Hi Tom - FYI

my "copycmd" variable coming up with value as below.

  /*lets take your .dat example*/

copy c:\temp\testfile.dat c:\nosuchdirectory\testfile.dat        

/*do you think we have to include " " mark in between command like below and it would do so...*/

copy "c:\temp\testfile.dat c:\nosuchdirectory\testfile.dat"

Super Contributor
Super Contributor
Posts: 358

Re: transferring files using (proc format+control table+ location)

Tom -  " Bingo........ :-) "

i think what it was bothering is i have added one more path for proc format,

"New Jersey"="f:\transfer\mdb\New Jersey"

so i think code was not accepting space in between New and Jersey....

I just change it to NJ instead of New Jersey for folder path and it's working fine now with input @ in last data _null_ statement.

Thanks a lot Tom!!!

Still Tom - if we want to include path ending as "New Jersey" ("New Jersey"="f:\transfer\mdb\New Jersey") - is there any alternative? Because i am afraid them will allow me to change the folder path since lot of people are working for same...


Also - code is not taking any space in between path also - i have checked it. let's say if i have path like, "New Jersey"="f:\transfer mdb\New Jersey" then it will not take it because i have space in b/w transfer and mdb in path.

Thanks!

Super User
Super User
Posts: 7,046

Re: transferring files using (proc format+control table+ location)

In DOS if you want to include filenames and directories with embedded spaces you need to quote the names.

copy "from file name" "f:\transfer\mdb\New Jersey"

To add the quotes in SAS you can use the QUOTE() function.

copycmd = catx(' ','copy'

                  ,quote(cats("&path\", filename))

                  ,quote(cats(targetdir))

               );

Super Contributor
Super Contributor
Posts: 358

Re: transferring files using (proc format+control table+ location)

Thanks a lot Sir...you are awesome...!!!  its big help for me...!!!

All set now...!!!

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 502 views
  • 0 likes
  • 2 in conversation