BookmarkSubscribeRSS Feed
mmea
Quartz | Level 8

Hi

If i have an Excel "Covid19_report &todaysdate. &time..xlsx" file  in a folder called F:\A

and I want to put this exact file into folder F:\B but duplicated 6 times like this:

"1_Covid19_report &todaysdate. &time..xlsx"

"2_Covid19_report &todaysdate. &time..xlsx"

"3_Covid19_report &todaysdate. &time..xlsx"

"4_Covid19_report &todaysdate. &time..xlsx"

"5_Covid19_report &todaysdate. &time..xlsx"

"6_Covid19_report &todaysdate. &time..xlsx"

 

is there a macro to do that?

13 REPLIES 13
CarmineVerrell
SAS Employee

You will need to have x command enabled to get this to work. To verify if you have x command enabled. Try and execute this statement in a program editor. If your server runs on windows environment ,   x "DIR"; 

or  if your server runs on Linux environment ,  x "ls";

 

If code works above then you should be able to use example below. If it doesn't then you will need to speak to you SAS administrator to get x commands enabled for this to work.

 

options noxwait;

data _null_;
/* x "copy C:\temp\book1.xlsx  C:\temp\book2.xlsx";   or  */
rc= system("copy C:\temp\book1.xlsx C:\temp\book2.xlsx");
rc= system("copy C:\temp\book1.xlsx C:\temp\book3.xlsx");
rc= system("copy C:\temp\book1.xlsx C:\temp\book4.xlsx");
put rc=;
run;

 

 

mmea
Quartz | Level 8

Thanks the X "dir" works on my computer

mmea
Quartz | Level 8

Should i just replace my file name with the ones you have put in the code?

mmea
Quartz | Level 8

Can you help me with the example file i showed .. im quite new in sas

CarmineVerrell
SAS Employee

Now unfortunately, you are not really learning anything if i do it all for you. 😞 

 

options noxwait;

data _null_;
/* x "copy c:\temp\testdata.txt c:\temp\testdata_&date..txt";*/
rc= system("copy f:\a\Covid19_report &todaysdate. &time..xlsx f:\b\1_Covid19_report &todaysdate. &time..xlsx");
rc= system("copy f:\a\Covid19_report &todaysdate. &time..xlsx f:\b\2_Covid19_report &todaysdate. &time..xlsx");
rc= system("copy f:\a\Covid19_report &todaysdate. &time..xlsx f:\b\3_Covid19_report &todaysdate. &time..xlsx");
rc= system("copy f:\a\Covid19_report &todaysdate. &time..xlsx f:\b\4_Covid19_report &todaysdate. &time..xlsx");
rc= system("copy f:\a\Covid19_report &todaysdate. &time..xlsx f:\b\5_Covid19_report &todaysdate. &time..xlsx");
put rc=;
run;

 

I would highly recommend you take some SAS training as even though I gave you the solution, you might end up with an error and won't be able to solve it. 

The best thing to do is to try to make yourself as self-sufficient as possible and the only way to do this is with educational training.  We have several online training courses that you can choose from. Based on your coding question i would recommend you start with the Programming 1 class. There is a free e-learning version available which is a great starter course. From there I would recommend you also take the 2-day Programming 2 class. Followed by a SAS 2-day Macro 1 class.

mmea
Quartz | Level 8
data _null_;
/* x "copy C:\temp\book1.xlsx  C:\temp\book2.xlsx";   or  */
rc= system("copy F:\A\COVID-19  Report &todaysdate. &ftime..xlsx 
           F:\B\1081_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\A\COVID-19 Report &todaysdate. &ftime..xlsx 
           F:\B\1082_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\A\COVID-19 Report &todaysdate. &ftime..xlsx 
           F:\B\1083_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\A\COVID-19 Report &todaysdate. &ftime..xlsx 
           F:\B\1084_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\A\COVID-19   Report &todaysdate. &ftime..xlsx 
           F:\B\1085_COVID-19  Report &todaysdate. &ftime..xlsx");
put rc=;
run;

i did this

mmea
Quartz | Level 8

Thank you! is i am learning of course 🙂

mmea
Quartz | Level 8

Unfortunealty it did not give any output, but neither any errors 😕

 

CarmineVerrell
SAS Employee

The code worked for me, so not sure why your code isn't. 

mmea
Quartz | Level 8

i think it could be something with the x command? when i run th code it shows me the prompt and says that the file do not exist

mmea
Quartz | Level 8

%let todaysDate = %sysfunc(today(), yymmdd10.);
%put &todaysDate;

data _null_;
ftime = intnx('hour',time(),0,'b');
cftime = compress(put(ftime,hhmm.),":");
call symputx("ftime",cftime);
run;

%put &=ftime.;

options noxwait;
data _null_;
rc= system("copy F:\Data\COVID-19 Report &todaysdate. &ftime..xlsx 
           F:\brugere\1081_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\Data\COVID-19  Report &todaysdate. &ftime..xlsx 
           F:\brugere\1082_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\Data\\COVID-19 Report &todaysdate. &ftime..xlsx 
           F:\brugere\1083_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\Data\COVID-19 Report &todaysdate. &ftime..xlsx 
           F:\brugere\1084_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\Data\COVID-19  Report &todaysdate. &ftime..xlsx 
           F:\brugere\1085_COVID-19  Report &todaysdate. &ftime..xlsx");
put rc=;
run;

I ran this

it gives me no error, but no outputs

ballardw
Super User

Show the name of the file you are copying. Copy the name an paste it into a text box opened with the forum's </> icon.

The text box is important because the main message windows on this forum will reformat text and may remove characters.

 

You need to be extremely careful with paths. The code you show here if the first copy worked the second wouldn't because you have a different number of spaces after COVID-19 in the second copy command.

 

rc= system("copy F:\Data\COVID-19 Report &todaysdate. &ftime..xlsx 
           F:\brugere\1081_COVID-19 Report &todaysdate. &ftime..xlsx");
rc= system("copy F:\Data\COVID-19  Report &todaysdate. &ftime..xlsx 
           F:\brugere\1082_COVID-19 Report &todaysdate. &ftime..xlsx");

 

  Hint: make the string represented by using something like:

%put copy F:\Data\COVID-19 Report &todaysdate. &ftime..xlsx
F:\brugere\1081_COVID-19 Report &todaysdate. &ftime..xlsx  ;

Then copy that result from the log and paste into a command line box on your system.

I suspect you may find that spaces in the path are part of the problem and you get to deal with quoting to make valid paths.

 

data _null_;
   insert = "&todaysdate. &ftime.";
   length copystr targstr commandstr $ 200;
   copystr =  cats(catx(' ','copy "F:\Data\COVID-19 Report',insert),'.xlsx"');
   do loop = 1081 to 1085;
       targstr = Quote(cats(catx(' ',cats("F:\brugere\",loop,"_COVID-19 Report"),insert),".xlsx"));
       put copystr=;
       put targstr=;
       /* uncomment the following after testing the above*/
/*       rc = system (catx(' ',copystr, targstr));*/

   end; 
run;

copy some of the results of the PUT to see if they work with the copy command in a command window.

 

Or use the data step to write a command file with the text of the copy commands. Then, as a text, execute the command file from a command prompt/window and see what you get. When you have the syntax correct you could then use the X or SYSTEM to execute the batch file that was written. With care you would then have documentation of what was actually written.

 

Are you actually getting multiple of these files every day? If not you could simplify things a tad by using the source date followed by an * to reference the source file.

With the Windows copy command if you specify a target directory (folder) with a source file then the same named file gets copied to the directory. So need to use the date or time at all in the target:

 

copy c:\thisfolder\thatfolder\somefilename.xlsx c:\targetfolder

will make of copy of "somefilename.xlsx" in folder c:\targetfolder

Which should simplify things a bit and reduce places to make type errors.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 1000 views
  • 0 likes
  • 3 in conversation