topic Re: put the same excel file with different names in a folder in New SAS User

put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 13:18:50 GMT

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?

Re: put the same excel file with different names in a folder

CarmineVerrell — Wed, 03 Feb 2021 13:56:52 GMT

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;

Re: put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 13:58:41 GMT

Thanks the X "dir" works on my computer

Re: put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 13:59:40 GMT

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

Re: put the same excel file with different names in a folder

CarmineVerrell — Wed, 03 Feb 2021 14:00:15 GMT

Yes

Re: put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 14:01:17 GMT

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

Re: put the same excel file with different names in a folder

CarmineVerrell — Wed, 03 Feb 2021 14:11:04 GMT

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.

Re: put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 14:12:21 GMT

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

Re: put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 14:13:10 GMT

Thank you! is i am learning of course 🙂

Re: put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 15:19:00 GMT

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

Re: put the same excel file with different names in a folder

CarmineVerrell — Wed, 03 Feb 2021 15:23:58 GMT

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

Re: put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 15:26:26 GMT

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

Re: put the same excel file with different names in a folder

mmea — Wed, 03 Feb 2021 15:31:26 GMT


%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

Re: put the same excel file with different names in a folder

ballardw — Wed, 03 Feb 2021 16:58:00 GMT

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.