Hi All, Would be much appreciated to solve this.I have input data (csv format) with different date in server. say for exp Cbi_CP_23032017 and Cbi_CP_24032017.I need to fetch that data on regular basis in SAS DI by using 'User written'. Data will be coming everyday with systemdate-1 manner and i need to fetch that. What is the code should I go with?
Hi @shubha1babu
Glad to hear that this was helpful. No need for apologies. I know better than I'd like to how stressful things can become when a milestone approaches.
If what I've posted was the solution: Please mark it as such so that this track gets marked as resolved.
Thanks,
Patrick
Hi:
Do you have a working program to start? What code have you tried? Here's an example of how you could create macro variables to capture today's date and yesterday's date.
cynthia
With DIS user written code is your last resort and you should only go for it if you can't find another way of doing things.
Your use case is nothing out of the ordinary and there are multiple approaches possible. What they all have in common: Use a SAS macro variable as part of your filename in the External File metadata definition; and choose this filename to be embedded in double quotes so it resolves during run-time.
Here how you can formulate the filename so you'll get a date part which is the system date minus 1 day.
<path>/Cbi_CP_%sysfunc(sum(%sysfunc(date()),-1),ddmmyyn8.)
First define your external file using an existing .csv and implement your DIS job (external file, file reader, target table). Once that works replace the filename in the external file with the dynamic version as posted above.
Now.... That's going to work if you can always run your code on the day where it's supposed to run. You can't rely on this for a production worthy implementation. There is always the possibility that the system is down for a day or that upstream systems don't provide the data on the day they should. You need to design and implement for catch-up runs.
The way I'd be doing this:
Implement a control table which maintains the days (dates) you've already processed. Implement a custom transformation which maintains this table (derive the date to be processed (=runasofdate), status of processing etc.); you could also implement a 2nd control table which maintains the processing status of every single .csv.
One of the things the custom transformation should do for you (controlled by prompts): Derive the runasofdate for you and pack it into one or multiple macro variable. You then place this custom transformation as the first node in your job.
Lets say this custom transformation returns a macro varialbe &runasofdate_ddmmyy. You can then use this variable as part of the filename in your External File metadata object like:
<path>/Cbi_CP_&runasofdate_ddmmyy
And at the very end of your daily process flow call your custom macro and have it update your control table with runasofdates.
This way you could execute multiple catchup runs on the same day without any dependency on the system date.
Hi @shubha1babu
Glad to hear that this was helpful. No need for apologies. I know better than I'd like to how stressful things can become when a milestone approaches.
If what I've posted was the solution: Please mark it as such so that this track gets marked as resolved.
Thanks,
Patrick
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.