cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Zatere
‎07-26-2024
  • 73 Posts
  • 23 Likes Given
  • 0 Solutions
  • 3 Likes Received

Re: Cumulative Amount based on sequential dates

by Quartz | Level 8 in SAS Programming
‎02-01-2022 03:40 AM
‎02-01-2022 03:40 AM
Thank you it works and it is really fast. I guess I would have to learn more about arrays. Thanks again. ... View more

Re: Cumulative Amount based on sequential dates

by Quartz | Level 8 in SAS Programming
‎01-28-2022 06:02 PM
‎01-28-2022 06:02 PM
Thanks for the code. It works but as you mentioned it is not efficient with big data. I can split the dataset and run it in smaller data. But wondered if there is any other way. ... View more

Re: Cumulative Amount based on sequential dates

by Quartz | Level 8 in SAS Programming
‎01-26-2022 04:14 AM
‎01-26-2022 04:14 AM
Hello,    Below my data:   data what_I_want; input ID $ DT:date9. Amount Sum_of_Next_2_Days; format DT date9.; datalines; A 09JUL2021 3600 3600 A 03AUG2021 456 489 A 04AUG2021 33 268 A 06AUG2021 235 335 A 07AUG2021 100 186 A 09AUG2021 86 86 A 12AUG2021 456 456 A 24AUG2021 22 1925 A 25AUG2021 987 1984 A 26AUG2021 916 1551 A 27AUG2021 81 828 B 07AUG2021 554 992 B 08AUG2021 193 438 B 09AUG2021 245 245 ; run; Along the line I would like something like that.  For each date, I want to find the sum of its next 2 days. Find for next 2 dates: For 09Jul, sum Amount where DT Between 09Jul and 11Jul For 03Aug, sum Amount where DT Between 03Aug and 05Aug For 04Aug, sum Amount where DT Between 04Aug and 06Aug For 06Aug, sum Amount where DT Between 06Aug and 08Aug For 07Aug, sum Amount where DT Between 07Aug and 09Aug For 09Aug, sum Amount where DT Between 09Aug and 11Aug For 12Aug, sum Amount where DT Between 12Aug and 14Aug For 24Aug, sum Amount where DT Between 24Aug and 26Aug For 25Aug, sum Amount where DT Between 25Aug and 27Aug For 26Aug, sum Amount where DT Between 26Aug and 28Aug For 27Aug, sum Amount where DT Between 27Aug and 29Aug For 07Aug, sum Amount where DT Between 07Aug and 09Aug For 08Aug, sum Amount where DT Between 08Aug and 10Aug For 09Aug, sum Amount where DT Between 09Aug and 11Aug     ... View more

Re: Cumulative Amount based on sequential dates

by Quartz | Level 8 in SAS Programming
‎01-25-2022 07:43 AM
‎01-25-2022 07:43 AM
Hi, thank you so much for your reply. It is not exaclty what I wanted. How can I get the sum of the next, lets say, 2 days of each day? This is what I was looking for (when I want to find the sum of 2 days in a row): data what_I_want; input ID $ DT:date9. Amount Sum_of_Next_2_Days; format DT date9.; datalines; A 09JUL2021 3600 0 A 03AUG2021 456 0 A 04AUG2021 33 489 A 06AUG2021 235 268 A 07AUG2021 100 335 A 09AUG2021 86 186 A 12AUG2021 456 0 A 24AUG2021 22 0 A 25AUG2021 987 0 A 26AUG2021 916 1925 A 27AUG2021 81 1984 ; run; It should work similarly if I want to find the sum of 3 days in a row and so on. Thanks. ... View more

Re: Cumulative Amount based on sequential dates

by Quartz | Level 8 in SAS Programming
‎01-25-2022 05:52 AM
‎01-25-2022 05:52 AM
Hi, I didnt consider everyting and I am wondering if I could ask some additinal help. As is, the program calculates the cumulative amount only for 1 sequential dates. For instance, 03AUG2021 & 04AUG2021 which is 489. I also wanted to be able to calculate the cumulative amount for more than 1 sequential dates. For example: - for two sequential dates: 04AUG2021 & 06AUG2021 = 268 - for three sequential dates: 25AUG2021 & 26AUG2021 & 27AUG2021 = 3909. I tried to change the dt=sum(dt0,2), but it doesnt work because it will not consider any dates between, meaning it will calculate the cumulative amount for only the pairs: 04AUG2021,06AUG2021 and 07AUG2021,09AUG2021. Any help please? ... View more

Cumulative Amount based on sequential dates

by Quartz | Level 8 in SAS Programming
‎01-24-2022 10:03 AM
‎01-24-2022 10:03 AM
Hello there,   I would like to calculate the cumulative amount based on sequential dates.   Please see below the data I have:   data have; input ID $ DT:date9. Amount; format DT date9.; datalines; A 09JUL2021 3600 A 03AUG2021 456 A 04AUG2021 33 A 06AUG2021 235 A 07AUG2021 100 A 09AUG2021 86 A 12AUG2021 456 A 24AUG2021 22 A 25AUG2021 987 A 26AUG2021 916 A 27AUG2021 81 ; run; I want a variable that represents the cumulative amount of the sequential dates. E.g.,  03AUG2021 & 04AUG2021 are sequential and therefore the cumulative amount is  489.   Please see below the data I want:   data want; input ID $ DT:date9. Amount Variable_Wanted; format DT date9.; datalines; A 09JUL2021 3600 0 A 03AUG2021 456 0 A 04AUG2021 33 489 A 06AUG2021 235 0 A 07AUG2021 100 335 A 09AUG2021 86 0 A 12AUG2021 456 0 A 24AUG2021 22 0 A 25AUG2021 987 1009 A 26AUG2021 916 1903 A 27AUG2021 81 997 ; run; Ideally, the user should be able to select the number of sequential dates to include.    Please see below the closest I can make:   data wanted; set have; new_Amount = 0; by ID DT; if dif(DT) <= 1 then /*how many sequential*/ do; new_Amount = Amount; end; run; Any help would much be appreciated.   Thanks. ... View more

Re: Date macro from calculation

by Quartz | Level 8 in SAS Programming
‎01-24-2022 03:37 AM
‎01-24-2022 03:37 AM
Thank you very much. I was hoping that I can use the %PUT statement. However, your solution does what I wanted, meaning to print the value. ... View more

Re: Date macro from calculation

by Quartz | Level 8 in SAS Programming
‎01-23-2022 12:58 PM
‎01-23-2022 12:58 PM
The macro mnth_1=intnx('month', &myDT., -1, 's') is used to create a variable in an existing dataset. As you might have seen, this part of the program works as expected. However, I would like to write the &mnth_1. Is that possible? Thanks. ... View more

Date macro from calculation

by Quartz | Level 8 in SAS Programming
‎01-23-2022 11:58 AM
‎01-23-2022 11:58 AM
Hello All,   I have the below program:     %let myDT = '23JAN2022'D; %macro dts(mnth_1=); data want; test_dt=&mnth_1.; format test_dt date9.; run; %put @@@&myDT; %put @@@&mnth_1; %mend; %dts(mnth_1=intnx('month', &myDT., -1, 's'));     Initially, I have the macro variable &myDT. which I pass it to the macro %dts(mnth_1=intnx('month', &myDT., -1, 's')) in order to find last month's date.     Even though I am able to use the &mnth_1. in order to create the dataset, when I use the %PUT statement to write the value it doesn't work.   Any ideas please? ... View more

PROC REPORT Display columns names as dates

by Quartz | Level 8 in SAS Programming
‎01-05-2021 08:14 AM
‎01-05-2021 08:14 AM
Hello,   I have the following data: data have; input condition $ 1. _6 _5 _4 _3 _2 _1; datalines; A 14 14 8 0 0 0 B 3904 3918 3926 3926 3926 3926 C 910 917 917 917 918 922 D 1821 1816 1824 1825 1837 1824 E 5 2 2 2 3 3 F 1.8 1.8 1 0 0 0 ; run; I then create dates formats:   DATA _NULL_; CALL SYMPUT ('_6Day',PUT(today() - 6,DATE9.)); CALL SYMPUT ('_5Day',PUT(today() - 5,DATE9.)); CALL SYMPUT ('_4Day',PUT(today() - 4,DATE9.)); CALL SYMPUT ('_3Day',PUT(today() - 3,DATE9.)); CALL SYMPUT ('_2Day',PUT(today() - 2,DATE9.)); CALL SYMPUT ('_1Day',PUT(today() - 1,DATE9.)); RUN; Then I create the report: PROC REPORT DATA= have ; define _6 / display "&_6Day"; define _5 / display "&_5Day"; define _4 / display "&_4Day"; define _3 / display "&_3Day"; define _2 / display "&_2Day"; define _1 / display "&_1Day"; run; However, when I change the dates formats then I don't get the desired output:   DATA _NULL_; CALL SYMPUT ('_6Day',PUT(today() - 6,DDMMYY10.)); CALL SYMPUT ('_5Day',PUT(today() - 5,DDMMYY10.)); CALL SYMPUT ('_4Day',PUT(today() - 4,DDMMYY10.)); CALL SYMPUT ('_3Day',PUT(today() - 3,DDMMYY10.)); CALL SYMPUT ('_2Day',PUT(today() - 2,DDMMYY10.)); CALL SYMPUT ('_1Day',PUT(today() - 1,DDMMYY10.)); RUN; PROC REPORT DATA= have ; define _6 / display "&_6Day"; define _5 / display "&_5Day"; define _4 / display "&_4Day"; define _3 / display "&_3Day"; define _2 / display "&_2Day"; define _1 / display "&_1Day"; run; I get this: while I want to get the below:   Condition 30/12/2020 31/12/2020 01/01/2021 02/01/2021 03/01/2021 04/01/2021 A 14 14 8 0 0 0 B 3904 3918 3926 3926 3926 3926 C 910 917 917 917 918 922 D 1821 1816 1824 1825 1837 1824 E 5 2 2 2 3 3 F 1.8 1.8 1 0 0 0   It seems that the format  DDMMYY10.  changes how the report is displayed. Is there any way to fix it?   Thanks.   ... View more

PROC REPORT merge columns

by Quartz | Level 8 in SAS Programming
‎12-09-2020 04:32 AM
‎12-09-2020 04:32 AM
Hello there,   I want to merge two columns so that they are shown as one when using PROC REPORT.     data have; input condition $ Today Yesterday Diff; datalines; AAA 18 17 1 BBB 3524 3541 -17 CCC 773 781 -8 DDD 1721 1721 0 EEE 26 26 0 FFF 5 3 2 ; run; PROC REPORT DATA=have; COLUMN condition Today ('Yesterday' Yesterday Diff); run;     The output of the above is:     What I want is:   Any ideas please? Thanks   ... View more

Re: Concatenate Dates to create variable names

by Quartz | Level 8 in SAS Programming
‎08-12-2020 09:56 AM
‎08-12-2020 09:56 AM
At the moment I just exported in excel. In the future I will be using PROC REPORT. I will make a research to find if I can use labels instead of names.. ... View more

Re: Concatenate Dates to create variable names

by Quartz | Level 8 in SAS Programming
‎08-12-2020 09:48 AM
‎08-12-2020 09:48 AM
Thanks. The ... AS "Quantity of &current_monthV"n worked as expected. In terms of ... AS my_valid_name label="Quantity of &current_monthV", the label is created but the column name is not shown as wanted. So when the report is delivered is not as expected... ... View more

Concatenate Dates to create variable names

by Quartz | Level 8 in SAS Programming
‎08-12-2020 09:31 AM
‎08-12-2020 09:31 AM
Hello, I have the following data set:   data have; input Product_Name $ Quantity Dates :date9.; format Dates date9.; datalines; A 50 01-Jun-20 A 165 01-May-20 A 10 01-Jul-20 B 200 01-Jun-20 B 200 01-May-20 B 50 01-Jul-20 C 113 01-Jun-20 C 145 01-May-20 C 23 01-Jul-20 ; run;   I want to create for each  Product_Name  a column that represents the total quantity in each month. This report always holds data from the last three months.  I want to automate and make it dynamic by using the dates. Thus I have created the following table with the most recent date of the report:   data myDate; Current_Date = '01JUL2020'D; format Current_Date date9.; run; And I have created macro variables as follows:     DATA _NULL_; set myDate; CALL SYMPUT ('current_month',PUT(Current_Date,BEST.)); CALL SYMPUT ('PREVIOUS_MONTH1',intnx('month',Current_Date,-1)); CALL SYMPUT ('PREVIOUS_MONTH2',intnx('month',Current_Date,-2)); CALL SYMPUT ('current_monthV',PUT(Current_Date,yymmdd10.)); CALL SYMPUT ('PREVIOUS_MONTH1V',PUT(intnx('month',Current_Date,-1),yymmdd10.)); CALL SYMPUT ('PREVIOUS_MONTH2V',PUT(intnx('month',Current_Date,-2),yymmdd10.)); RUN;   I use the below code   proc sql; create table want as SELECT Product_Name ,SUM(CASE WHEN Dates = &current_month THEN Quantity end) as "&current_monthV"n ,SUM(CASE WHEN Dates = &PREVIOUS_MONTH1 THEN Quantity end) as "&PREVIOUS_MONTH1V"n ,SUM(CASE WHEN Dates = &PREVIOUS_MONTH2 THEN Quantity end) as "&PREVIOUS_MONTH2V"n ,SUM(Quantity) AS Quanity_TOTAL FROM have group by 1 ;quit;   This gives me the below: Product_Name 2020-07-01 2020-06-01 2020-05-01 Quanity_TOTAL A  10  50  165  225 B 50 200 200  450 C 23  113  145 281   What I want is:   Product_Name Quantity of 2020-07-01 Quantity of 2020-06-01 Quantity of 2020-05-01 Quanity_TOTAL A  10  50  165  225 B 50 200 200  450 C 23  113  145 281   I have tried this but it didn't work: SUM(CASE WHEN Dates = &current_month THEN Quantity end) as cat("Quantity of ","&current_monthV") Any ideas pleas?   Thanks   ... View more

PROC SORT does not sort as expected

by Quartz | Level 8 in SAS Programming
‎03-13-2020 04:13 PM
‎03-13-2020 04:13 PM
Hi I hope you all are well.   I am having the following issue.   I ve got a table that contains ProductID, Sale_Value, Quantity on which I take the sum of Sale_Value and Quantity on ProductID to create a table as below:   ProductID Sum of Sale_Value Sum of Quantity 1 5687.35 68 2 326.42 73 3 4478.55 29 4 2967.39 12 5 4478.55 88   Later I sort the data as follows:   PROC SORT DATA = have; BY DESCENDING SUM_OF_SALE_VALUE DESCENDING SUM_OF_QUANTITY; RUN: The expected output would the below:   ProductID Sum of Sale_Value Sum of Quantity 1 5687.35 68 5 4478.55 88 3 4478.55 29 4 2967.39 12 2 326.42 73   However, I am getting the below:   ProductID Sum of Sale_Value Sum of Quantity 1 5687.35 68 5 4478.55 29 3 4478.55 88 4 2967.39 12 2 326.42 73   I have checked the raw data and indeed the totals for ProductIDs 5 and 3 are as shown above, so I rule out the possibility of hidden rounding numbers.   For some reason that I cannot understand the PROC SORT does not give the desired output.   Any ideas please? Thanks ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎07-26-2024 05:15 AM
Likes given to
Likes from
View All
Quick links