SAS VA - filter to current year and month by default

[DharmeshTadvi]

how to show current year and month data by default on my VA report , but also give user the possibility to filter other year and month. (Note year and month should change dynamically when new month or year come )

[SASKiwi]

The easiest way to do this is to add a flag in your SAS VA table before loading it. For the current month, set this flag to 0, for the previous month 1, and the month before that 2 and so on. Then you can have a static filter in VA set to month flag = 0.

[sridhar_m]

Hi,

Thanks a lot for your quick updates. Could you please suggest how to include and where to include to add a flags. 

Thanks in Advance,

Sridhar

[SASKiwi]

Before loading your data into VA a simple DATA step can add this column. I'm assuming you would have a reporting date column in your data. From that you can derive a reporting month column. Based on the logic below Jan 2019 data will be flagged with a reporting month (Reporting_Month) of 0.

data want;
  set have;
  Reporting_Month = intck('MONTH', Reporting_Date, "31Jan2019"d);
run;

[sridhar_m]

Thanks a lot for your quick updates. 

[sridhar_m]

Hi,

Could you please help me on the below:

How to load latest 6 months data into SAS CAS Server.

and after that how to append 3 more months into it.

Using with PROC CASUTIL.

Thanks in Advance

[SASKiwi]

Have you read the CASUTIL documentation?

https://documentation.sas.com/?docsetId=casref&docsetTarget=n03spmi9ixzq5pn11lneipfwyu8b.htm&docsetV...

It looks like you can do an append with the LOAD statement to add to an existing CAS table. A normal LOAD with a WHERE condition to create the table in the first instance should work.

[sridhar_m]

Hi,

Thank you very much for your answer.

I am submitting the below code:

proc casutil;

   load casdata=" TRANSACTIONS" casout=" TRANSACTIONS_STG" outcaslib=" FA" incaslib="FA"

   options={where=" dw_processed_date='201902'd " }

   replace ;

quit;

But, I am not able to apply format. Kindly help.

Thank you

[34reqrwe]

Hi,

have you read the documentation?

what does your log say?

[sridhar_m]

82 load casdata="FA_TRANSACTIONS" incaslib="FA2" outcaslib="FA2" casout="FA_TRANSACTIONS_STG"
83 dataSourceOptions=(dbmswhere="posted_on_dt=201902")
84 replace;
NOTE: Action failed during Embedded Process processing. Retrying action without the Embedded Process.
ERROR: General error org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: RuntimeException Cannot
convert to Date from: int
ERROR: The action stopped due to errors.

Thank you

[SASKiwi]

Please post your SAS log.

[sridhar_m]

Hi,

PFB for your reference:

82 load casdata="FA_TRANSACTIONS" incaslib="FA2" outcaslib="FA2" casout="FA_TRANSACTIONS_STG"
83 dataSourceOptions=(dbmswhere="posted_on_dt=201902")
84 replace;
NOTE: Action failed during Embedded Process processing. Retrying action without the Embedded Process.
ERROR: General error org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: RuntimeException Cannot
convert to Date from: int
ERROR: The action stopped due to errors.

[34reqrwe]

Sounds like a syntax error .
"Cannot convert to Date from: int"
you could confirm if it is syntax by removing this line
dataSourceOptions=(dbmswhere="posted_on_dt=201902")