How to 'find' a Dataset in a library and do a matching look up conditionally?

Reply
Frequent Contributor
Posts: 137

How to 'find' a Dataset in a library and do a matching look up conditionally?

Dear Folks, I am in need of a solution that should do a look up for an equivalent dataset that is named past as opposed to the current output being named present.  I have a daily, weekly and monthly ETL SAS process that typically extracts data from multiple sources into a dataset. After certain, validation and transformation I am coping the final output in a folder or in other words library to have it permanently stored.

The final output I get after each run is named as Present and have added a suffix using a macro variable that I take Customer_ID value from the dataset. This is done to make it a unique named dataset so that when the process is run everytime a new dataset gets stored in the library. So for example:

data libref.final_output_present_&customer_id;   /*in this example: Permanent library is libref and &customer_id will resolve as the unique identifier for the dataset*/

set input;

-all transformations blah blah blah---;

run;

After the above is run, the compare process is bound to follow However, before I the compare process executes I need the look up solution in the library and the Logic for my requirement:

1. Now,I am in need of a solution that should look for the  equivalent dataset named Past(libref.final_output_past_&customer_id) in the same library. So,here the differentiating word is past as opposed to present and the identifier being customer_id should match.

2. Look up solution should find the match, and if the match is found then execute the compare process.

3. So something like this should make sense i hope *if libref.final_output_past_&customer_id exists in the libref then do: execute compare process:end;else do skip compare process and just do load process;end;

I'd greatly appreciate this help as this would really mean a lot.

Many thanks,

Charlotte

Super User
Posts: 11,343

Re: How to 'find' a Dataset in a library and do a matching look up conditionally?

Posted in reply to CharlotteCain

It appears that you are doing this for a specified customer id. If so something like this might be a workable start:

%macro DoCompare(id= );

     %if %sysfunc(exist(libref.final_output_past_&id)) %then %do;

          <here would go the code for when the file exists>

     %end;

     %else %do;

          <whatever you do when it isn't found>

     %end;

%mend;

And call using:

%DoCompare(id=&customer_id);

Ask a Question
Discussion stats
  • 1 reply
  • 172 views
  • 3 likes
  • 2 in conversation