08-18-2015 02:35 PM
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*/
-all transformations blah blah blah---;
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.
08-18-2015 03:24 PM
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>
<whatever you do when it isn't found>
And call using: