Copy dataset from one library to another based on dataset name prefix

[Soham0707]

Hello,

I need to copy the dataset from one library to another but it is based on the prefix of the dataset name.

For example; I have library “Libref_all” and I have below datasets in this library,

Libref_all

A_DM1.sas7bdat

A_DM2.sas7bdat

A_DM3.sas7bdat

A_DM4.sas7bdat

B_DM1.sas7bdat

B_DM2.sas7bdat

B_DM3.sas7bdat

B_DM4.sas7bdat

C_DM1.sas7bdat

C_DM2.sas7bdat

D_DM1.sas7bdat

I want to move these datasets based on their names, like dataset having prefix “A_” should copy to “Libref_A” library, similarly “B_” should copy to “Libref_b”, “C_” should copy to “Libref_C” and “D_” should copy to “Libref_D”.

Thanks in advance.

[PeterClemmensen]

Try this. This builds a Proc Copy call for each obs. You can see the call in the data set. 

Uncomment the Call Execute part and see if it works.

data have;
input Libref_all $20.;
datalines;
A_DM1.sas7bdat
A_DM2.sas7bdat
A_DM3.sas7bdat
A_DM4.sas7bdat
B_DM1.sas7bdat
B_DM2.sas7bdat
B_DM3.sas7bdat
B_DM4.sas7bdat
C_DM1.sas7bdat
C_DM2.sas7bdat
D_DM1.sas7bdat
;

data callstack;
   set have;
   ds = scan(Libref_all, 1);
   lb = cats("Libref_", char(Libref_all, 1));

   call = compbl(cat(
      "proc copy in = Libref_all out = ", lb, "memtype=data;
          select ", ds, ";
       run;"
    ));
  *call execute (call);
run;

[Soham0707]

Thanks PeterClemmensen.

I have more than 1000 datasets to copy based on the name. Is there any other way to do this without datalines or cards statement?
I have provided an example that describes the scenario.

[PeterClemmensen]

The code will work without the Datalines/Cards Statement. They are purely for demonstration. 

Try running the code against your actual data and check the callstack data. Does the call variable contain Proc Copy steps that look alright?

If so, try uncommenting the Call Execute Statement and see what happens. 

[yabwon]

If you have datasets listed like you presented, you could try something like this:

data _null_;
input Libref_all $ 20.;

call execute(
"data Libref_" !! scan(Libref_all,1,"_") !! '.' !! scan(Libref_all,1,".") !! "; "
!! "set Libref_all." !! scan(Libref_all,1,".") !! "; run;"
!! "proc delete data = Libref_all." !! scan(Libref_all,1,".") !! "; run;"
);

cards;
A_DM1.sas7bdat
A_DM2.sas7bdat
A_DM3.sas7bdat
A_DM4.sas7bdat
B_DM1.sas7bdat
B_DM2.sas7bdat
B_DM3.sas7bdat
B_DM4.sas7bdat
C_DM1.sas7bdat
C_DM2.sas7bdat
D_DM1.sas7bdat
;
run;

the `call execute()` will produce the code for you. I'm assuming you don't have any indexes and datasets aren't compressed.

Bart

[Ksharp · Accepted solution]

proc copy in=library_all out=library_a ;
select a_: ;
run;
proc copy in=library_all out=library_b ;
select b_: ;
run;

And Make a macro to go through all the table .

[Soham0707]

Thank you so much Ksharp. it's working.