BookmarkSubscribeRSS Feed
DharmeshTadvi
Fluorite | Level 6

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 )

12 REPLIES 12
SASKiwi
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

Thanks a lot for your quick updates. 

sridhar_m
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
Quartz | Level 8

Hi,

have you read the documentation?

what does your log say?

 

sridhar_m
Obsidian | Level 7

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
PROC Star

Please post your SAS log.

sridhar_m
Obsidian | Level 7

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
Quartz | Level 8
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")

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 4432 views
  • 3 likes
  • 4 in conversation