How to collect files with a collection rule

[Linlin · Accepted solution]

try:

data list;
input fname $ id $;
cards;
1122 bg
1620 bg
1660 bg
0520 tz
0240 tr
0280 tr
0560 tr
;run;

data have;
input o fname $   id $;
cards;

                        1     1122     bg   
                        2     1620     bg   
                        3     1660     bg   
                        4     0240     tr   
                        5     0280     tr   
                        6     0560     tr   
                        7     0520     tz   
                        8     1122     tz 
      ;
     

proc sql;
create table final_selection as
select a.*
from have a,list b
where a.fname=b.fname and a.id=b.id;
  quit;
proc print;
run;
                                       Obs    o    fname    id

                                        1     1    1122     bg
                                        2     2    1620     bg
                                        3     3    1660     bg
                                        4     4    0240     tr
                                        5     5    0280     tr
                                        6     6    0560     tr
                                        7     7    0520     tz

[tesu]

Thanks, Linlin. Your code solves the problem.

[Ksharp]

Sorry. My bad. Linlin, you win.

proc sql;

select path

  from have

   where catx(' ', fname, id)  in (select catx(' ', fname,id)  from list);

quit;

[Linlin]

:smileylaugh::smileylaugh::smileylaugh:!

[tesu]

Thanks, guys! You're amazing!

[tesu]

Thanks for getting back to the discussion. I've learned a lot from you!

[Tom]

I am not sure what the issue is.  Just check if f2\0222 is in the filenames.  Probably would be easier to see the solution if the columns in your CSV file were in the same order as they appear in the filenames.

data filelist ;

  input fname $ folder $ @@;

cards;

0222 f2 0345 f2 2345 f1 3423 f3 4444 f1 4827 f3 5635 f3 6767 f1 7623 f3

run;

data filenames;

  infile 'dir *.dbf /s/b' pipe truncover;

  input path $80.;

run;

proc sql noprint ;

  create table want as select * from filenames

    where catx('\',scan(path,-2,'\'),scan(path,-2,'.\')) in

     (select catx('\',folder,fname)) from filelist)

  ;

quit;