BookmarkSubscribeRSS Feed
SKG
Obsidian | Level 7 SKG
Obsidian | Level 7

Hi Guys,

 

Is there any way if i select a date and get the date from start of the month date to till selected date in Cross tab or list table object?

 

An example:

Selected Date: 23-Sep-2016 (Slider Object - Single Value) not Multiple

 

And get the Date from 01-Sep-2016 to 23-Sep-2016 with Revenue too....

 

 

Regards,

Sunny Kumar Gupta

5 REPLIES 5
PrakashSH
Calcite | Level 5

Hi,

 

If i am not wrong you need the list of dates from first of the month to the selected date .

I have just tried writing a macro to achieve this.

PFB the macro:-

 

%macro Till_date(as_of_date=);

data _null_;

call symput("first_of_month",put(intnx('month',&as_of_date,0),date9.));

%put &first_of_month ;

run;

 

data date_till_day(keep=list_date);

do i="&first_of_month"d to &as_of_date ;

list_date=i;

format list_date date9.;

output;

end;

run;

 

%mend;

 

%Till_date(as_of_date="15SEP2016"d);

 

After running the macro by passing the required date at the last line where we call a macro you get the "date_till_day" dataset with all the dates in it till now.

 

Hope this helps. If not kinldy let me know so that i can correct it.

 

Regards,

Prakash

SKG
Obsidian | Level 7 SKG
Obsidian | Level 7

Hi Prakash,

 

Thank for helping...

 

I know this through macro but i want to do in SAS VA not through Base SAS.

 

Regards,

Sunny Kumar Gupta

Frank_Boekamp
Quartz | Level 8

Having the intnx function in SAS VA would be great. This has been discussed earlier in this forum. Hopefully SAS will implement this soon.

 

 

Sam_SAS
SAS Employee

Hello Sunny,

 

It is possible that the RelativePeriod operator in an aggregated measure would be useful here, if you specify an offset of zero and you specify _ToDate_ as the scope.

 

https://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n1lxnqfip132can1hdzu...

 

I am not sure though whether the scope date can be specified via a report parameter, or if it must be hardcoded in your expression.

 

Let us know if that helps.

 

Thanks,

Sam

SKG
Obsidian | Level 7 SKG
Obsidian | Level 7

Hi Guys,

 

Sorry for the delay but I found the solution, what I did.

 

First, I took Slider for "select date" for single value

select date.png

 

Then I took drop-down list object for the same date but converted in character format. You know very well that Drop-Down list object can't take Date/Datetime. So, for that reason, I converted the same date into character format and assign to drop-down list and create a parameter (Full Date_Char Parameter) to pass value.

select date char.png

 

And the right side panel, select "Required" option and create interaction between these two objects Slider and Drop-Down list.

 

interaction.png

 

Then I take a list table to display a revenue value.

 

Now, Select the same list table and go to right side panel "Filters" tab and click on "Add Filter" and create 

 

monthly.png

 

Now what I'm doing here

Let an example:-

 

We're going to take the same date : 23-Sep-2016 (an example Numeric value of the date- 206)

 

 

Pass the drop down list value which is a parameter (Full Date_Char Parameter) in the Parse function convert the character to Date format and then TeastAs function convert Date to numeric format. 

numeric.png

 

you'll get value - 206

 

DayofMonth function converts the date to Day and then subtract by 1.

 

dayofmonth.png

 

you'll get value from this formula:- (23 -1) = 22.

 

Subtract these two values and get the start of the month date..

 

numeric to dayof month.png

 

Value :- 184 (1-Sep-2016)

 

The betweeninclisive function gives the range.

 

184 to 206 (1-sep-2016 to 23-sep-2016)

 

Through this the way you can get the date from the start of the month date to till selected date.

Regards,

Sunny Kumar Gupta

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 5 replies
  • 1885 views
  • 0 likes
  • 4 in conversation