topic Re: Copy dataset from one library to another based on dataset name prefix in SAS Programming

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

Soham0707 — Tue, 02 Feb 2021 10:03:40 GMT

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.

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

PeterClemmensen — Tue, 02 Feb 2021 10:18:11 GMT

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;

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

yabwon — Tue, 02 Feb 2021 10:19:10 GMT

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

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

Soham0707 — Tue, 02 Feb 2021 10:55:03 GMT

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.

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

PeterClemmensen — Tue, 02 Feb 2021 11:00:43 GMT

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.

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

Ksharp — Tue, 02 Feb 2021 12:01:51 GMT

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 .

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

Soham0707 — Tue, 02 Feb 2021 12:30:01 GMT

Thank you so much Ksharp. it's working.