topic Re: Last month and YTD data in SAS Enterprise Guide

Last month and YTD data

_el_doredo — Sun, 21 Aug 2022 13:22:44 GMT

Hello Experts,

I am having this year's data starting from Jan to July as of now. My Data gets updated on monthly basis. On next month i will have data up to August.

I have a following variables

1. Month

2. Store_Name

3. Sales

I want my following variables in my report

1. Month(In Filter)

2. Store_Name

3. Month_Sales(Using Sales Variable)

4. Sales_YTD(Using Sales Variable)

Like Below

In the month Filter, If i select May means in month_sales i need to see May month Sales and in YTD Sales i need to see Jan to May month overall sales.. Like wise if i change it to March, In Month Sales i need to see March Month Sales and in YTD i need to see Jan to March month over all sales.

It would be helpful if anyone provide some logic to it

Thanks in Advance

Re: Last month and YTD data

PaigeMiller — Sun, 21 Aug 2022 13:35:10 GMT

Are your month numeric variables with values 1 through 12, or are they character strings such as 'January 2022', or are they actual numeric SAS date values?

Can you show us (part of) the SAS data set you will be using? I don't know what that screen capture is, where it is from, but we can't work from it.

Can you work with SAS code in Enterprise Guide, or do you have to work only from the menus/GUI?

Re: Last month and YTD data

_el_doredo — Sun, 21 Aug 2022 13:38:22 GMT

My Month is MONNAMEw. format(Jan,Feb,Mar). Actually the screenshot which i added here is from Excel Pivot. My end result is on Pivot only. I want logic on SAS. So that i can use it in Excel to create pivot

Thanks

Re: Last month and YTD data

PaigeMiller — Sun, 21 Aug 2022 14:03:18 GMT

We can't work from Excel. Don't bother showing us Excel in the future. Show us your SAS data set.

Here is SAS logic assuming the year is 2022. This is UNTESTED CODE as I don't have your data.

proc format;
    value ytdf. (multilabel) '01JAN2022'd-'31JAN2022'd='January 2022 YTD'
        '01JAN2022'd-'28FEB2022'd='February 2022 YTD'
        '01JAN2022'd-'31MAR2022'd='March 2022 YTD'
        ... /* you type the rest, I'm lazy */
        ;   /* Don't forget the semi-colon */
run;
proc summary data=have;
    class month/mlf;
    var sales;
    format class ytdf.;
    output out=want sum=ytd_sales;
run;

Also, you can create pivot tables in SAS, no need to create pivot tables in Excel.

Re: Last month and YTD data

_el_doredo — Sun, 21 Aug 2022 14:03:10 GMT

Hello,

I have attached my sample data here. Now using this same data i want to have Month and Year as Filter(As i need to select any month and Year i want).

Now, If i am running my code now, Default i want Month_Sales as July month and YTD as (Jan to Jul). If i select Year as 2021 and Month as Sep in filter, I need my Month_Sales as 2021 Sept month overall sales and YTD as (Jan 2021 to Sept 2021 Overall Sales)

I hope i am clarifying my question to you

Please import my Sample data

Re: Last month and YTD data

PaigeMiller — Sun, 21 Aug 2022 14:13:05 GMT

No no no. I said we can't work from Excel. Attaching an Excel file is totally useless here.

You need to show us (a portion of) your SAS data set. You need to provide it as SAS data step code, which you can type in yourself (example), or you can create this SAS data step code via these instructions. That's the only form acceptable. Any other form is not acceptable.

Apparently, you did not even try my code on your data. Please do that.

Re: Last month and YTD data

_el_doredo — Sun, 21 Aug 2022 14:17:18 GMT

Hello @PaigeMiller ,

I have attached Data step code here use the data.

Data Raw;
infile cards;
input Year month $ Store_Name Sales;
cards;
2021 Jan 534 250
2021 Jan 768 275
2021 Jan 556 300
2021 Jan 899 325
2021 Jan 544 350
2021 Feb 534 375
2021 Feb 768 400
2021 Feb 556 425
2021 Feb 899 450
2021 Feb 544 475
2021 Mar 534 500
2021 Mar 768 525
2021 Mar 556 550
2021 Mar 899 575
2021 Mar 544 600
2021 Apr 534 625
2021 Apr 768 650
2021 Apr 556 675
2021 Apr 899 700
2021 Apr 544 725
2021 May 534 750
2021 May 768 775
2021 May 556 800
2021 May 899 825
2021 May 544 850
2021 Jun 534 875
2021 Jun 768 900
2021 Jun 556 925
2021 Jun 899 950
2021 Jun 544 975
2021 Jul 534 1000
2021 Jul 768 1025
2021 Jul 556 1050
2021 Jul 899 1075
2021 Jul 544 1100
2021 Aug 534 1125
2021 Aug 768 1150
2021 Aug 556 1175
2021 Aug 899 1200
2021 Aug 544 1225
2021 Sep 534 1250
2021 Sep 768 1275
2021 Sep 556 1300
2021 Sep 899 1325
2021 Sep 544 1350
2021 Oct 534 1375
2021 Oct 768 1400
2021 Oct 556 1425
2021 Oct 899 1450
2021 Oct 544 1475
2021 Nov 534 1500
2021 Nov 768 1525
2021 Nov 556 1550
2021 Nov 899 1575
2021 Nov 544 1600
2021 Dec 534 1625
2021 Dec 768 1650
2021 Dec 556 1675
2021 Dec 899 1700
2021 Dec 544 1725
2022 Jan 534 1750
2022 Jan 768 1775
2022 Jan 556 1800
2022 Jan 899 1825
2022 Jan 544 1850
2022 Feb 534 1875
2022 Feb 768 1900
2022 Feb 556 1925
2022 Feb 899 1950
2022 Feb 544 1975
2022 Mar 534 2000
2022 Mar 768 2025
2022 Mar 556 2050
2022 Mar 899 2075
2022 Mar 544 2100
2022 Apr 534 2125
2022 Apr 768 2150
2022 Apr 556 2175
2022 Apr 899 2200
2022 Apr 544 2225
2022 May 534 2250
2022 May 768 2275
2022 May 556 2300
2022 May 899 2325
2022 May 544 2350
2022 Jun 534 2375
2022 Jun 768 2400
2022 Jun 556 2425
2022 Jun 899 2450
2022 Jun 544 2475
2022 Jul 534 2500
2022 Jul 768 2525
2022 Jul 556 2550
2022 Jul 899 2575
2022 Jul 544 2600
;
run;

I will try to run the code which you sent to me.. Thank you so much

Re: Last month and YTD data

PaigeMiller — Sun, 21 Aug 2022 14:23:46 GMT

This is not what you said earlier. You said:

My Month is MONNAMEw. format(Jan,Feb,Mar).

So if your data is not MONNAMEw. format, then the code I provided won't work. Please clarify this.

Re: Last month and YTD data

_el_doredo — Sun, 21 Aug 2022 14:28:52 GMT

Sorry my bad.. Month is character variable only. It doesn't have any formats.

Re: Last month and YTD data

PaigeMiller — Sun, 21 Aug 2022 15:19:16 GMT

proc format;
    value $ytdf. (multilabel) 
        'Jan'='January 2022 YTD'
        'Jan','Feb'='February 2022 YTD'
        'Jan','Feb','Mar'='March 2022 YTD'
        ... /* you type the rest, I'm lazy */
        ;   /* Don't forget the semi-colon */
run;
proc summary data=have nway;
    class month/mlf;
    class year;
    var sales;
    format class $ytdf.;
    output out=want sum=ytd_sales;
run;

Re: Last month and YTD data

_el_doredo — Sun, 21 Aug 2022 15:45:07 GMT

Thanks @PaigeMiller , It helps a lot

Re: Last month and YTD data

_el_doredo — Mon, 22 Aug 2022 09:18:55 GMT

Hello @PaigeMiller ,

I have multiple classes here..Like year,Store_Name,Area,State and also i have multiple variables to check for Month and YTD.

The code which you provided will get me YTD data. But, along that i need Month data as well.

Classes : Year,Month,Store_Name,Area,State

Var : Sales,Payment,click,steps ( All are numeric only)

Now i want to find Month and YTD for these.

If i select May 2022 means I need to see May 2022 month data for all VAR variables and (Jan 2022 to May 2022 Overall data) for all VAR variables

I hope you get my question here

Re: Last month and YTD data

PaigeMiller — Mon, 22 Aug 2022 10:19:01 GMT

Just add these extra variables into PROC SUMMARY. The class variables go in the CLASS statement. The analysis variables go in the VAR statement. If you have four VAR variables, then the output statement must have four variable names. Give it a try.

Re: Last month and YTD data

_el_doredo — Mon, 22 Aug 2022 10:38:38 GMT

Yeah i tried that i worked well for YTD. But, I need Month wise data as well. If i create the same for Month then if i merge with YTD means i am getting lots of Duplicate values for Month Data

Re: Last month and YTD data

PaigeMiller — Mon, 22 Aug 2022 10:41:22 GMT

@_el_doredo wrote:

Yeah i tried that i worked well for YTD. But, I need Month wise data as well. If i create the same for Month then if i merge with YTD means i am getting lots of Duplicate values for Month Data

If you also need month results (not YTD but just the one month), then you need to run the PROC SUMMARY code again without the MLF option and without the FORMAT statement.

Re: Last month and YTD data

_el_doredo — Mon, 22 Aug 2022 10:43:15 GMT

Lemme try it Thanks @PaigeMiller