BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
2222
Calcite | Level 5

Hi everyone,

I'm trying to take specific files from specific folders and copy them into different specific folders. What I have so far is (am getting errors: "Libref 94AZ is not assigned" and "SQL View BBB.mio98 and BBB.mio97 could not be processed because at least one of the data sets, or views, referenced bu it could not be located, or opened successfully" (but they are definitely there and I'm not using it currently):


/*macro to move a file from a quarter's data directory (AAA & BBB) into an ABC directory*/
%macro copy_two(qtrname, year, filename1, filename2, filename3, filename4, filename5, filename6, filename7, filename8);
libname AAA "\\path" access=readonly;
libname BBB "\\path2" access=readonly;
libname CCC "\\ABC directory";

data Res.&filename1.; set AAA.&filename1.;
data Res.&filename2.; set BBB.&filename2.;
data Res.&filename3.; set BBB.&filename3.;
data Res.&filename4.; set BBB.&filename4.;
data Res.&filename5.; set BBB.&filename5.;
data Res.&filename6.; set BBB.&filename6.;
data Res.&filename7.; set BBB.&filename7.;
data Res.&filename8.; set BBB.&filename8.;
run;

 

%mend copy_two; /*end of macro*/

/*macro that loops through quarters calling the copy_two macro*/


%macro loop;
%do i = 19 %to 19; /*loop through as many years as you like. If just one year type %do i = 20 %to 20;*/
%copy_two(Mar, &i., att99, mio98, mio97, mio96, mio95, mio94, mio93, mio92); /*repeat the macro call to move another file name*/
%copy_two(Jun, &i., att99, mio98, mio97, mio96, mio95, mio94, mio93, mio92);
%copy_two(Sep, &i., att99, mio98, mio97, mio96, mio95, mio94, mio93, mio92);
%copy_two(Dec, &i., att99, mio98, mio97, mio96, mio95, mio94, mio93, mio92);
%end;

 

%mend loop; /*end of macro*/
run;

/*call the loop macro to run*/
%loop;
run

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@2222 wrote:
also wondering the order of proc copy in=res out=oed;
is this taking files from res and putting into oed or from oed and to res?

The documentation will tell you that:

COPY Procedure 

 

Hint: in= means stuff that goes into a procedure, while out= is used for stuff that comes out of it.

 

You are right, I mixed up the the libraries. The PROC COPY statement should be:


proc copy in=oed out=res;

 

View solution in original post

12 REPLIES 12
ballardw
Super User

Show your actual macro code. The code you show will not generate that error, likely because you have not included one or more of the "path" or directory pieces of the code. You show macro parameters QTRNAME and YEAR in the definition of the macro Copy_two but not where they are used. Since part of your values involved in an error is 94 one assumes you have misused a "year" value. But since you have not shown how you use Year or Qtrname it is hard to say.

 

To get a better idea where you went wrong set Options mprint; before executing the macro loop. That will show the code generated by the macro processor and the error will likely appear in much closer proximity to the problem location and you can see what what happened.

 

If you can't figure it out the copy the entire log of with the Mprint on. Then on the forum open a text box using the </> icon above the message window and paste the entire copied text. The text box is important to keep the forum software from reformatting text pasted into the main window that makes following the errors and any diagnostics harder.

 

If you feel that your path names are so sensitive that you can't share them this is going to take a lot of extra work to diagnose.

 

Make sure that all your paths and file names start at a drive/mount point. If you are running with a server then the path will have to be relative to the server settings.

2222
Calcite | Level 5

hello thank you for the replies, it worked fine when it was filename1 and filename2 but adding the others is what broke it, this is the code exactly as is:


/*macro to move a file from a quarter's data directory into an LFC directory*/
%macro copy_two(qtrname, year, filename1, filename2, filename3, filename4, filename5, filename6, filename7, filename8);
libname OED "\\sasnasprd\Data\S288\LCI&qtrname.&year\PRD\DATA\AT06" access=readonly;
libname Muj "\\sasnasprd\Data\S288\LCI&qtrname.&year\PRD\DATA" access=readonly;
libname Res "\\sasnasprd\Data\S288\LFC\Sample review 2022\&qtrname.&year.";

data Res.&filename1.; set muj.&filename1.;
data Res.&filename2.; set oed.&filename2.;
data Res.&filename3.; set oed.&filename3.;
data Res.&filename4.; set oed.&filename4.;
data Res.&filename5.; set oed.&filename5.;
data Res.&filename6.; set oed.&filename6.;
data Res.&filename7.; set oed.&filename7.;
data Res.&filename8.; set oed.&filename8.;
run;

%mend copy_two; /*end of macro*/

/*macro that loops through quarters calling the copy_two macro*/
%macro loop;
%do i = 19 %to 19; /*loop through as many years as you like. If just one year type %do i = 20 %to 20;*/
%copy_two(Mar, &i., muj90, eue10, eue20, euj30, euj40, eue90, due90, mue90); /*repeat the macro call to move another file name*/
%copy_two(Jun, &i., muj90, eue10, eue20, euj30, euj40, eue90, due90, mue90);
%copy_two(Sep, &i., muj90, eue10, eue20, euj30, euj40, eue90, due90, mue90);
%copy_two(Dec, &i., muj90, eue10, eue20, euj30, euj40, eue90, due90, mue90);
%end;

 

%mend loop; /*end of macro*/
run;

/*call the loop macro to run*/
%loop;
run

Kurt_Bremser
Super User

You can simplify your code by using proc copy for a series of datasets within a library:

%macro copy_two(qtrname, year, filename1, filename2, filename3, filename4, filename5, filename6, filename7, filename8);

libname OED "\\sasnasprd\Data\S288\LCI&qtrname.&year\PRD\DATA\AT06" access=readonly;
libname Muj "\\sasnasprd\Data\S288\LCI&qtrname.&year\PRD\DATA" access=readonly;
libname Res "\\sasnasprd\Data\S288\LFC\Sample review 2022\&qtrname.&year.";

data Res.&filename1.;
set muj.&filename1.;
run;

proc copy in=res out=oed;
select
  &filename2.
  &filename3.
  &filename4.
  &filename5.
  &filename6.
  &filename7.
  &filename8.
;
run;
%mend copy_two;

%macro loop(start,end;

%do i = &start. %to &end.;
%copy_two(Mar, &i., muj90, eue10, eue20, euj30, euj40, eue90, due90, mue90);
%copy_two(Jun, &i., muj90, eue10, eue20, euj30, euj40, eue90, due90, mue90);
%copy_two(Sep, &i., muj90, eue10, eue20, euj30, euj40, eue90, due90, mue90);
%copy_two(Dec, &i., muj90, eue10, eue20, euj30, euj40, eue90, due90, mue90);
%end;
 
%mend loop;

%loop(20,20)

Please run this and post the complete log by copy/pasting it into a window opened with this button:

Bildschirmfoto 2020-04-07 um 08.32.59.jpg

2222
Calcite | Level 5

Thank you so much Kurt, I really appreciate your help!

Can I please check, before I run your elegant code, in the proc copy step you have in=res out=oed, since the files are coming from muj and oed folders should muj get a mention here along with oed?

Kurt_Bremser
Super User

In your example:

data Res.&filename1.; set muj.&filename1.;
data Res.&filename2.; set oed.&filename2.;
data Res.&filename3.; set oed.&filename3.;
data Res.&filename4.; set oed.&filename4.;
data Res.&filename5.; set oed.&filename5.;
data Res.&filename6.; set oed.&filename6.;
data Res.&filename7.; set oed.&filename7.;
data Res.&filename8.; set oed.&filename8.;

only the first dataset comes from muj. This is still handled in a data step. But since all others come from the same library, PROC COPY makes the job much easier.

2222
Calcite | Level 5

Hi Kurt,

Thank you but I'm not sure what you mean, why don't we need to tell sas that muj is one of the folders? Even though only one files is coming out of muj don't we still need to tell sas about it so that file is copied as well as all the oed ones?

Kurt_Bremser
Super User

@2222 wrote:

Hi Kurt,

Thank you but I'm not sure what you mean, why don't we need to tell sas that muj is one of the folders? Even though only one files is coming out of muj don't we still need to tell sas about it so that file is copied as well as all the oed ones?


The data step copies the lone dataset from muj, so we do not need to include it in PROC COPY.

2222
Calcite | Level 5
also wondering the order of proc copy in=res out=oed;
is this taking files from res and putting into oed or from oed and to res?
Kurt_Bremser
Super User

@2222 wrote:
also wondering the order of proc copy in=res out=oed;
is this taking files from res and putting into oed or from oed and to res?

The documentation will tell you that:

COPY Procedure 

 

Hint: in= means stuff that goes into a procedure, while out= is used for stuff that comes out of it.

 

You are right, I mixed up the the libraries. The PROC COPY statement should be:


proc copy in=oed out=res;

 

Kurt_Bremser
Super User

At least some (if not all) of your datasets are in fact views and reference datasets for which the necessary library is not currently defined.

For each such ERROR, see how the LIBNAME should be defined.

2222
Calcite | Level 5

You're right but how did you know many of the datasets were views? So because of that i'm getting the errors for almost each dataset:

 

NOTE: Copying RES.EUE10 to OED.EUE10 (memtype=DATA).
ERROR: Unable to create OED.EUE10.DATA because OED.EUE10.VIEW already exists.

 

how do i get around this please?

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 12 replies
  • 2091 views
  • 0 likes
  • 3 in conversation