topic Re: how to use array to pass information , assign two libraries and copy some datasets from src1 to in SAS Enterprise Guide

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

alepage — Mon, 16 Aug 2021 20:06:36 GMT

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;

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

Reeza — Mon, 16 Aug 2021 20:09:13 GMT

libname function + CALL EXECUTE instead.

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

alepage — Mon, 16 Aug 2021 20:27:42 GMT

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

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

Reeza — Mon, 16 Aug 2021 20:29:03 GMT

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.

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

ballardw — Mon, 16 Aug 2021 20:35:42 GMT

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.

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

Reeza — Mon, 16 Aug 2021 23:46:35 GMT

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;

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

Tom — Tue, 17 Aug 2021 00:39:50 GMT

Just write the code you want to generate to a file. Then once you get it working you just use %INCLUDE to run the code.

Use CATX() or CAT() to simplify constructing the names.

%let path1=FOLDER1;
%let path2=FOLDER2;
filename code temp;
data _null_;
  length path file $200 ;
  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;
      put ;
      path = catx('/',"&path1.",lcp(i),bc(j));
      put "libname src1  spde " path :$quote. ';' ;
      path = catx('/',"&path2.",lcp(i),bc(j));
      put "libname dest1 spde " path :$quote. ';' ;
      put 'proc datasets lib=scr1;';
      put '  copy out=dest1 memtype=data;' ;
      put '  select ' ;
      do k=1 to dim(ld);
        fname= cats(lcp(i),bc(j),'_prm_d',ld(k));
        put @5 fname ; 
      end;
      put '  ;';
      put '  run;';
      put 'quit;';
    end;
  end;
run;
%include code / source2;

Also instead of the arrays you can use the power of the DO loop.

Consider things like:

do lcp = 'A', 'B' ,'C' ,'D' ,'E';
do date='29JUL2021'd to '06AUG2021'd;

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

Kurt_Bremser — Tue, 17 Aug 2021 11:16:00 GMT

So you basically want to loop over dates and copy datasets from one location to the other? You can do that with a few nested loops and the FCOPY function.

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

Tom — Tue, 17 Aug 2021 13:28:06 GMT

@Kurt_Bremser wrote:

So you basically want to loop over dates and copy datasets from one location to the other? You can do that with a few nested loops and the FCOPY function.

Good idea for normal files, but probably not the right way to copy SPDE datasets.

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

alepage — Tue, 17 Aug 2021 15:48:38 GMT

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;

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

Reeza — Tue, 17 Aug 2021 16:06:47 GMT

Change your IF to a macro conditional %IF instead.

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

alepage — Tue, 17 Aug 2021 18:38:39 GMT

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?

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

Reeza — Tue, 17 Aug 2021 18:51:24 GMT

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

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

alepage — Tue, 17 Aug 2021 19:29:24 GMT

%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;

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;

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

Reeza — Tue, 17 Aug 2021 20:11:56 GMT

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.....

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

alepage — Tue, 17 Aug 2021 20:26:42 GMT

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

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

alepage — Tue, 17 Aug 2021 21:00:39 GMT

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

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

Kurt_Bremser — Tue, 17 Aug 2021 21:30:53 GMT

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;

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

Tom — Tue, 17 Aug 2021 23:12:04 GMT

You don't seem to understand how CALL EXECUTE() works and when the generated code works. That is another reason to use the write a file method. You are much less likely to get confused about when the generated code will run.

It would probably be easier to create a macro that confirms the list of files before trying to copy them.

Something like:

%macro copy_members(inlib,outlib,memlist);
proc sql noprint;
select nliteral(memname) into :memlist separated by ' '
from dictionary.members
where libname="%upcase(&inlib)"
  and memtype='DATA'
  and findw("&memlist",memname,' ','sit')
;
quit;
%if &sqlobs %then %do; 
proc copy inlib=&inlib outlib=&outlib memtype=data;
   select &memlist ;
run;
%end;

%mend copy_members;

Example usage:

332   options mprint;
333   %copy_members(inlib=sashelp,outlib=work,memlist=cars class xyz);
MPRINT(COPY_MEMBERS):   proc sql noprint;
MPRINT(COPY_MEMBERS):   select nliteral(memname) into :memlist separated by ' ' from dictionary.members where libname="SASHELP" and
memtype='DATA' and findw("cars class xyz",memname,' ','sit') ;
MPRINT(COPY_MEMBERS):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds


MPRINT(COPY_MEMBERS):   proc copy inlib=sashelp outlib=work memtype=data;
MPRINT(COPY_MEMBERS):   select CARS CLASS ;
MPRINT(COPY_MEMBERS):   run;

NOTE: Copying SASHELP.CARS to WORK.CARS (memtype=DATA).
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set WORK.CARS has 428 observations and 15 variables.
NOTE: Copying SASHELP.CLASS to WORK.CLASS (memtype=DATA).
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

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

alepage — Wed, 18 Aug 2021 13:53:56 GMT

Thanks a lot Kurt for your completary information. Is there a way to check if a dataset exist in a do loop before making a proc datasets? Or course to make a list of available datasets is an option, I complety forgot to look at. But, the interest to be able to check if a dataset exist remain something, I would like to look at.

Best Regards