how to use array to pass information , assign two libraries and copy some datasets from src1 to dest

[alepage]

Hello,

What I would like to do, is to put some information into arrays then retreive this information, to assign two libraries , one src1 and one dest1 (there path will change into the loop), then copy a list of dataset from src1 to dest1.

Does someone can help me with my code?

%let path1=FOLDER1;
%let path2=FOLDER2;
data _null_;
length folderpath1 folderpath2 $60 fname $20 L 3.;
array ld {9} $8 ('20210729' '20210730' '20210731' '20210801' '20210802' '20210803' '20210804' '20210805' '20210806');
array lcp {5} $2 ('A' 'B' 'C' 'D' 'E');
array bc {2} $4 ('auto' 'habi');
do i=1 to dim(lcp);*do i=1 to 5;

if lcp(i) in ('A','B') then L=1;
else L=2;
put L;
do j=1 to L;*do j=1 to 2;
folderpath1="&path1."||'/'||lcp(i)||'/'||bc(j);
folderpath2="&path2."||'/'||lcp(i)||'/'||bc(j);

put folderpath1;
put folderpaht2;

/******* assign two libraries ****/
/* libname src1 spde folderpath1;*/
/* libname dest1 spde folderpath2;*/
/*********************************/


do k=1 to dim(ld);*do k=1 to 9;

fname= lcp(i)||bc(j)||'_prm_d'||ld(k);

put fname;
/* copy a some datasets from src1 to dest1*/
/*
proc datasets lib=scr1;
copy out=dest1 memtype=data;
select fname;
run;
*/


end;

end;
end;
run;

[Reeza]

libname function + CALL EXECUTE instead.

[alepage]

I am not sure, that I understand.  Could you please provide an example?

[Reeza]

You cannot use libname statements and proc datasets in your data step. Instead, use the LIBNAME function + DOSUBL to run the proc data sets code, otherwise, generally your code should work fine.

[Reeza]

Working off @ballardw code here's the rough idea. Those loops look a bit weird, and this is boing to be a PITA to debug. Personally, I do these by figuring out to do it for one set of paths. Convert that to a macro that does it for your input parameters of the file paths.

Build the list of folders into a data set using the folder path methods illustrated by @ballardW and then call the macro once for each line in your data set via call execute. I illustrate that here. 

Otherwise, I've sketched out a bit more of the idea below. Obviously can't test this without folders/data etc which is one of the reasons I also never use this type of approach.

%let path1=FOLDER1;
%let path2=FOLDER2;
data _null_;
length folderpath1 folderpath2 $60 fname $20 L 3.;
array ld {9} $8 ('20210729' '20210730' '20210731' '20210801' '20210802' '20210803' '20210804' '20210805' '20210806');
array lcp {5} $2 ('A' 'B' 'C' 'D' 'E');
array bc {2} $4 ('auto' 'habi');
do i=1 to dim(lcp);

   if lcp(i) in ('A','B') then L=1;
   else L=2;
   do j=1 to L;
      folderpath1="&path1."||'/'||lcp(i)||'/'||bc(j);
      folderpath2=Catx('/',"&path1.",lcp(i),bc(j) );
      put folderpath1=;
      put folderpath2=;
     *create libname for each folder path via libname function;
     rc_src=libname('src1', folderpath1);
     rc_dest = libname('dest1', folderpath2);
     rc_complete=    dosubl('proc datasets .....rest of your code for moving files');


   end;
end;
run;

[ballardw]

May want to learn the various CAT functions.

This

folderpath1="&path1."||'/'||lcp(i)||'/'||bc(j);

can be replaced with

folderpath1=Catx('/',"&path1.",lcp(i),bc(j) );

for example. The CATX function places the first parameter between all the other strings and trims trailing spaces which  may be pretty critical if you are creating or using paths for directory structures.

Example, note that the Folderpath2 (spelled correctly in the PUT statement) is just the same as Folderpath1 but using the CATX function. I didn't bother to attempt to clean up the rest of the code as part of this demonstration.

%let path1=FOLDER1;
%let path2=FOLDER2;
data _null_;
length folderpath1 folderpath2 $60 fname $20 L 3.;
array ld {9} $8 ('20210729' '20210730' '20210731' '20210801' '20210802' '20210803' '20210804' '20210805' '20210806');
array lcp {5} $2 ('A' 'B' 'C' 'D' 'E');
array bc {2} $4 ('auto' 'habi');
do i=1 to dim(lcp);

   if lcp(i) in ('A','B') then L=1;
   else L=2;
   do j=1 to L;
      folderpath1="&path1."||'/'||lcp(i)||'/'||bc(j);
      folderpath2=Catx('/',"&path1.",lcp(i),bc(j) );
      put folderpath1=;
      put folderpath2=;
   end;
end;
run;

The || operator will use the full length of a variable and pad with blanks to meet that. Extra blanks are seldom a good idea in file path structures and if you intend to read for existing paths the values you created might not match the needed values.

[alepage]

You will find my code below.  However, I would like to check if the dataset exist before making the proc datasets but 

my if statement does not seems to work.  Is there a way to correct that or an options with the proc datasets to avoid trying to copy a dataset that does not exist.

Please note that the call execute with the proc datasets seems to work properly except due to the if statement that is not

working properly, it may try to copy a dataset that does not exist giving error into the log file

/******* Check if a dataset exist using a macro function % and the macrovariable exist (1 for yes, 0 for no)*/
/* Example of call for the macro function %checkds : %checkds(sashelp.class); %put &=exist; *************/
%macro checkds(dsn);
%global exist;
%if %sysfunc(exist(&dsn)) %then
%do;
%let exist=1;
run;
%end;
%else
%do;
%let exist=0;
%end;
%mend checkds;

/***********************************************************/
%let path1=/source;
%let path2=/destination;

data _null_;
length folderpath1 folderpath2 $60 fname $20 L 3.;
array ld {9} $8 ('20210729' '20210730' '20210731' '20210801' '20210802' '20210803' '20210804' '20210805' '20210806');
array lcp {5} $2 ('A' 'B' 'C' 'D' 'E');
array bc {2} $4 ('auto' 'habi');
do i=1 to 1 /*dim(lcp)*/;*do i=1 to 5;
if lcp(i) in ('A','B') then L=1;
else L=2;
put L;
do j=1 to L;*do j=1 to L (1 or 2);
folderpath1=catx('/',"&path1.",lcp(i),bc(j));
folderpath2=catx('/',"&path2.",lcp(i),bc(j));

/******* assign two libraries ****/
call execute(cat('libname src1 spde'," ","'",folderpath1,"';"));
call execute(cat('libname dest1 spde'," ","'",folderpath2,"';"));

/********* copying the dataset for src1 to dest1 ************************/

do k=1 to dim(ld);

fname=cat(lcp(i),bc(j),'_prm_d',ld(k));
/* checking if the dataset exist */
call execute(cats('%checkds(src1.',fname,');'));
if "&exist." eq 1 then
do;
put fname;
call execute(cat('proc datasets lib=src1;','copy out=dest1 memtype=(data);','select ',cats(fname),';run;'));
end;
end;
end;
end;
run;

[Reeza]

Change your IF to a macro conditional %IF instead.

[alepage]

I have tried the %if and it did not work better.

it seems that the macro variable that the first value of the loop and that it is not updated for some reasons, I don't understand

Does someone has an idea why and or to solve that issue?

[Reeza]

You didn't provide the code for %checkds or your version with %IF ....

[alepage]

/******* Check if a dataset exist using a macro function % and the macrovariable exist (1 for yes, 0 for no)*/
/* Example of call for the macro function %checkds : %checkds(sashelp.class); %put &=exist; *************/

%macro checkds(dsn);
%global exit;
%if %sysfunc(exist(&dsn)) %then %do;
data _null_;
call symputx('exist',1);
run;

%end;

/***********************************************************/
%let path1=/source1;
%let path2=/dest1;

data _null_;
length folderpath1 folderpath2 $60 fname $20 L 3.;
array ld {9} $8 ('20210729' '20210730' '20210731' '20210801' '20210802' '20210803' '20210804' '20210805' '20210806');
array lcp {5} $2 ('A' 'B' 'C' 'D' 'E');
array bc {2} $4 ('auto' 'habi');
do i=1 to 1 /*dim(lcp)*/;*do i=1 to 5;
if lcp(i) in ('A','B') then L=1;
else L=2;
put L;
do j=1 to L;*do j=1 to L (1 or 2);
folderpath1=catx('/',"&path1.",lcp(i),bc(j));
folderpath2=catx('/',"&path2.",lcp(i),bc(j));

/******* assign two libraries ****/
call execute(cat('libname src1 spde'," ","'",folderpath1,"';"));
call execute(cat('libname dest1 spde'," ","'",folderpath2,"';"));

/********* copying the dataset for src1 to dest1 ************************/

do k=1 to dim(ld);

fname=cat(lcp(i),bc(j),'_prm_d',ld(k));
/* checking if the dataset exist */
call execute(cats('%checkds(src1.',fname,');'));
/* it seems that after the above call execute, the macro &exist. is not updated*/
put "the value of the flag is &exist.";
%if "&exist." eq 1 %then
%do;
put fname;
%end;
end;
end;
end;
run;

[Reeza]

Could you please comment your code and then I'll add the corrections/ updates. I noticed that your %IF doesn't actually include your CALL EXECUTE anymore.....

[alepage]

I have retreive the call execute to simplify.  

The idea is to check if the dataset exist.  If so, the put the name of the dataset into the logfile.

among the list of data set, for Company A, business line auto, I have the following possibilities.

Aauto_prm_d20200729

Aauto_prm_d20200730

Aauto_prm_d20200731

Aauto_prm_d20200801

Aauto_prm_d20200802

Aauto_prm_d20200803

Aauto_prm_d20200804

Aauto_prm_d20200805

Aauto_prm_d20200806

So it is all the possible dataset name into that folder, except the the Aauto_prm_d20200802 does not exist.

Therefore, what's I am trying to do is to use the call execute (%checkds(src1.fname)) to obtain the value of exist.

If the file exits then exits=1 otherwise exist=0

I that portion of the code

do k=1 to dim(ld);

fname=cat(lcp(i),bc(j),'_prm_d',ld(k));
call execute(cats('%checkds(src1.',fname,');'));
if "&exist." eq 1 then
do;
put fname;
end;
end;

I am trying to check if the dataset fname exist if so, put the fname into the logfile if not I don't want to see it.

When this codition will be respected, I will replace the put fname statement by:

call execute(cat('proc datasets lib=src1;','copy out=dest1 memtype=(data);','select ',cats(fname),';quit;'));

in order to copy fname from src1 to dest1

Again, if the file does not exit, I don't want this call execute to be perform, to avoid to get an error, as the file does not exits.

Up to now, it seem that when it enter into the loop , it take the first value of exist and keep it.

It is like in the loop, this call execute(cats('%checkds(src1.',fname,');')); is supposed to be executed but the macro variable exist is not updated.

I don't know what to do.

I hope you will be able to help

[alepage]

SYMBOLGEN: Macro variable DSN resolves to src1.Aauto_prm_d20210801
MLOGIC(CHECKDS): %IF condition %sysfunc(exist(&dsn)) is TRUE
MPRINT(CHECKDS): Data _null_;
MPRINT(CHECKDS): call symputx('exist',1);
MPRINT(CHECKDS): run;
MLOGIC(CHECKDS): %PUT "&=exist."
SYMBOLGEN: Macro variable EXIST resolves to 1
"EXIST=1"
MLOGIC(CHECKDS): Ending execution.
Aauto_prm_d20210801
MLOGIC(CHECKDS): Beginning execution.
MLOGIC(CHECKDS): Parameter DSN has value src1.Aauto_prm_d20210802
MLOGIC(CHECKDS): %GLOBAL EXITS
SYMBOLGEN: Macro variable DSN resolves to src1.Aauto_prm_d20210802
MLOGIC(CHECKDS): %IF condition %sysfunc(exist(&dsn)) is FALSE
MPRINT(CHECKDS): Data _null_;
MPRINT(CHECKDS): call symputx('exist',0);
MPRINT(CHECKDS): run;
MLOGIC(CHECKDS): %PUT "&=exist."
SYMBOLGEN: Macro variable EXIST resolves to 1
"EXIST=1"
MLOGIC(CHECKDS): Ending execution.
Aauto_prm_d20210802

[Kurt_Bremser]

do k=1 to dim(ld);

fname=cat(lcp(i),bc(j),'_prm_d',ld(k));
call execute(cats('%checkds(src1.',fname,');')); /* this pushes the macro onto the execution stack while the data step is running */
if "&exist." eq 1 then /* but this is evaluated when the data step is compiled, _before_ it executes */
do;
put fname;
end;
end;