DATA Step, Macro, Functions and more

how to read csv file with dynamically changed date

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

how to read csv file with dynamically changed date

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? 


Accepted Solutions
Solution
‎03-31-2017 04:26 AM
Respected Advisor
Posts: 4,173

Re: how to read csv file with dynamically changed date

Posted in reply to shubha1babu

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

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,864

Re: how to read csv file with dynamically changed date

Posted in reply to shubha1babu

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

get_yesterday_date.png

Respected Advisor
Posts: 4,173

Re: how to read csv file with dynamically changed date

[ Edited ]
Posted in reply to shubha1babu

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.

New Contributor
Posts: 2

Re: how to read csv file with dynamically changed date

Hello Patrick,
Sorrry for late reply.I was too busy for my project, we are in such a mode
of Golive in Production.

Your suggestion has worked finely.I am very much thankful to you and
really appreciate your quick support.

Thanks,
S Bakshi

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Solution
‎03-31-2017 04:26 AM
Respected Advisor
Posts: 4,173

Re: how to read csv file with dynamically changed date

Posted in reply to shubha1babu

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 189 views
  • 1 like
  • 3 in conversation