Populating new table with month end data only

[Soulbroda]

Hello guys. I am currently working on SAS DI Studio. Pretty much new at this.

Presently modifying a new job which pulls data from different sources and loads into different target tables. My present task is to add an extra target table that will contain only month end data. I added an extract transform and tried using the following logic in the where clause: "intnx('month',datepart(input("&BusinessDate_DTTM",datetime.)),0,'E');" but got this error "WARNING: Apparent symbolic reference BUSINESSDATE_DTTM not resolved. ERROR: INPUT function reported 'ERROR: Invalid year value' while processing WHERE clause." in the SAS log. What could I be doing wrongly?

[AMSAS]

Hi Soulbroda

The error is telling you that the macro variable &BusinessDate_DTTM has not been assigned (i.e. given a value)

Here's a couple of things you can try to help you resolve the issue

1) Turn on the Option SYMBOLGEN, this will write out to the log the value of macro variables when they are resolved

2) Replace the macro variable &BusinessDate_DTTM with a constant e.g. "01JAN2019 11:30:00"

Thanks

[Soulbroda]

For a clearer picture, the table I am loading to has the following columns: Businessdate, CustomerNum, AccountNum, RiskRating, ImpairedLoanFlag, CreateDateTime, JobName and ProcessedDateTime. The goal is to populate the table by only extracting month end records for every month's data that is being loaded. how do I write the logic and where will it be? Under the "Where" tab or beside the BusinessDate column in the mapping tab

[AMSAS]

Sorry, but that is still not clear to me. So let me ask some questions/statements

1) At some point, you have a SAS dataset that has date values in it. Probably after step 9 in your screenshot.
2) In that dataset you have a variable Businessdate that contains SAS datetime values
3) You want to extract only the observations where businessdate is the last day of the month (e.g. 31 Jan, 30 Apr, etc..)

If that's the case your where clause could be as simple as this 

day(datepart(intnx('dtday',date,1))) = 1

Here's an example datastep 

data test ;
	do date="28Jan2019 00:00"dt to "03FEb2019 00:00"dt by 60*60*9;
		if day(datepart(intnx('dtday',date,1))) = 1 then 
			put "Keep this Record" date= datetime.;
		else
			put "Not interested in this Record : " date= datetime.;
	end ;
run ;

Hope this helps

To explain the IF statement
1) It adds 1 day to the SAS datetime value in the date variable (intnx function)

2) It converts the SAS datetime value into a SAS date value

3) It checks if the SAS date value is the 1st of the month

[Soulbroda]

The data has all been pooled into the append transformation which then feeds the other tables using a table loader. The column in the table having the date is the "businessdate".

While it feeds records for everyday into all the other tables, I added an extract transformation to populate one table with only end of month records. The problem is getting the right logic to pull just month end records.

[Patrick · Accepted solution]

@Soulbroda 

From how I understand your explanations the logic should be looking somehow as below:

data daily;
  format businessdate datetime20.;
  do businessdate=intnx('dtday',datetime(),-40) to datetime() by 86400;
    output;
  end;
  stop;
run;
  
/* use SQL Extract Transformation */
proc sql;
  create view EoM as
    select *
    from daily
    where datepart(businessdate)=intnx('month',datepart(businessdate),0,'b')
    ;
quit;

/* use Table Loader Transformation */
proc append base=Monthly data=EoM;
run;

You probably need to think how to avoid adding the same monthly data twice (i.e. when re-running the flow for some reason).

You could either use the insert (skip updates) option in the table loader - but that's costly performance wise - or have some pre-processing checking for the max date already loaded and then have the monthly load within a condition or have a unique index on your monthly target table ....or something else.

As for your question where to place a condition (mapping expression or where clause): If you have a coding background then best always look into the DIS generated code as this will teach you efficiently what settings on the GUI side result in what changes to generated code.