About _el_doredo

_el_doredo · ‎08-22-2022

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

_el_doredo · ‎08-21-2022

Thanks @PaigeMiller , It helps a lot

_el_doredo · ‎08-21-2022

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

_el_doredo · ‎08-21-2022

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

_el_doredo · ‎08-21-2022

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

_el_doredo · ‎08-21-2022

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

_el_doredo · ‎08-21-2022

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

_el_doredo · ‎07-20-2022

Thank you so much for explaining clearly. It's a new learning for me

_el_doredo · ‎07-20-2022

Hello, It's always 364 days... Thanks a lot

_el_doredo · ‎07-20-2022

@FreelanceReinh , could you please explain more about 'day364.196' format

_el_doredo · ‎07-20-2022

Thank you so much. It helps a lot

_el_doredo · ‎07-15-2022

Hello Experts, I want to findout the followings 1. Previous month begin and end(In this case, June 1 to June 30 of 2022) 2. Current Year beginning 3. Last Year Beginning 4. Last Year previous month(In this case, June 1 to June 30 of 2021) If we following calendar year means then it's easy to find out. Below is my code for calendar year DATA _NULL_; call symput('pre_month_bn',compress(put(intnx('month',today(),-1,'beginning'),YYMMDDN.)))); call symput('pre_month_end',compress(put(intnx('month',today(),-1,'end'),YYMMDDN.)))); call symput('PY_pre_month_bn',compress(put(intnx('month',today(),-13,'beginning'),YYMMDDN.)))); call symput('PY_pre_month_end',compress(put(intnx('month',today(),-13,'end'),YYMMDDN.)))); call symput('Year_bn',compress(put(intnx('Year',today(),0,'beginning'),YYMMDDN.)))); call symput('PY_Year_bn',compress(put(intnx('Year',today(),-1,'beginning'),YYMMDDN.)))); run; But, We are following different date as year beginning. Date Weekday Calendar Yr Our Yr Complete Year 05-05-2016 Thursday 2016 2017 2017 starts from 05-05-2016 and ends at 05-03-2017 05-04-2017 Thursday 2017 2018 2018 starts from 05-04-2017 and ends at 05-02-2018 05-03-2018 Thursday 2018 2019 2019 starts from 05-03-2018 and ends at 05-01-2019 05-02-2019 Thursday 2019 2020 2020 starts from 05-02-2019 and ends at 04-29-2020 04/30/2020 Thursday 2020 2021 2021 starts from 04-30-2020 and ends at 04-28-2021 04/29/2021 Thursday 2021 2022 2022 starts from 04-29-2021 and ends at 04-27-2022 04/28/2022 Thursday 2022 2023 2023 starts from 04-28-2022 and ends at 04-26-2023 04/27/2023 Thursday 2023 2024 2024 starts from 04-27-2023 and ends at 04-25-2024 So if i want to find out begining of 2022 means it should fetch 04-29-2021. like wise for 2021 means it should fetch 04-30-2020. Please suggest some way to find out current year and last year without hardcoding dates. Now in this case i want to find beginning of 2023(Our year) and beginning of 2022(Our Year). suppose we crossed 04/27/2023 means it should automatically fetch beginning of 2024(Our year) and beginning of 2023(Our year) Thanks in Advance

_el_doredo · ‎07-15-2022

Sorry for the last response. Actually requirement is not right in this case.. Thanks for your help

_el_doredo · ‎07-01-2022

@PaigeMiller This is my code Proc sql; create table final as select b.orderdate, b.promo_type, b.payment_type, sum(b.Payment_Amt) as Payment_amt, sum(a.Sales_Amt) as Sales_amt from sales a left join payment b on a.orderdate=b.orderdate group by 1,2,3 ; quit;

_el_doredo · ‎07-01-2022

Hello Experts. I want to join/merge sales and payment data. But, my summarization data is giving me duplicate sales amount. I dont want that Datasets are below: data payment; infile cards dlm=',' dsd; input ticketid :$20. Promo_Type Payment_Type :$12. customer_id :$15. OrderDate :yymmdd10. Promo_Des :$30. Payment_Amt; format OrderDate yymmdd10.; cards; A098765,,credit,C014532,2021-05-11,,500 A098765,,cash,C014532,2021-05-11,,100 A098765,,cash,C014532,2021-05-11,,180 A098765,,check,C014532,2021-05-11,,200 A098765,8360,Finance,C014532,2021-05-11,"36 months",5000 A098765,8600,Finance,C014532,2021-05-11,"60 months",3500 ; run; data Sales; infile cards dlm=',' dsd; input ticketid :$20. ProductId :$20. customer_id :$15. OrderDate :yymmdd10. Sales_amt; format OrderDate yymmdd10.; cards; A098765,"AX-14589",C014532,2021-05-11,1570 ; run; I want my output like this Assume Two filters are there. 1.Payment_Type 2.Promo_Type Orderdate sum of sales_amt sum of payment_amt 11-05-2021 1570 9480 If i select select Payment_type as Cash means my output need to be Orderdate sum of sales_amt sum of payment_amt 11-05-2021 0 280 If i select Promo_Type as 8360 means my output need to be Orderdate sum of sales_amt sum of payment_amt 11-05-2021 0 5000 Can some one help me on this? Thanks in Advance

Online Status	Offline
Date Last Visited	2 weeks ago

Re: Transposing Data with Blank Rows and Repeating Instances

Re: How to print table name using proc fedsql

How to print table name using proc fedsql

Re: How to find count of each dataset

Re: How to find count of each dataset

How to find count of each dataset

I need to check whether the variable is available if yes means I need ...

Re: How to import only the first obs from each file into sas

Re: How to import only the first obs from each file into sas

How to import only the first obs from each file into sas

Re: How to print table name using proc fedsql

Re: How to print table name using proc fedsql

Re: How to print table name using proc fedsql

Re: How to find count of each dataset

Re: How to find count of each dataset

Re: How to use macro variable that created using CALL SYMPUT in anothe...

Re: How to use macro variable that created using CALL SYMPUT in anothe...

Re: Last month and YTD data

Re: Last month and YTD data

Re: Last month and YTD data

Re: Last month and YTD data

Re: Last month and YTD data

Re: Last month and YTD data

Last month and YTD data

Re: Finding YTD and Previous month

Re: Finding YTD and Previous month

Re: Finding YTD and Previous month

Re: Finding YTD and Previous month

Finding YTD and Previous month

Re: Sales and Payment Data Merging

Re: Sales and Payment Data Merging

Sales and Payment Data Merging