BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shubha1babu
Calcite | Level 5

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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

Patrick
Opal | Level 21

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.

shubha1babu
Calcite | Level 5
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. -##
Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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