Everyday a job runs and it creates a dataset with date extension in the library called final. For example date extension for the first record read this 2018 is the year, 05 is the month and 04 is the day which is yymmddn8.  date format   Sample_20180504 Sample_20180503 Sample_20180502 Sample_20180501 Sample_20180430 Sample_20180429 Sample_20180428   I want to create a macro or code which will delete the tables or dataset in the final library which has file extension more than 3 days old. In the above example all the dataset older than 2nd May 2018 extension should be deleted if I run it today which is 4th May 2018   I tried the code but it did not work out data _null_;   call symput('to_delete',catt('sample_',year(today())-0,    put(intnx('day',today(),-3), z2.))); run;   Similarly you can put for month but it only deletes one record which is less than 3 that is Sample_20180501. I want to delete all that are older than 3 days old which is till 28th april 2018 record       ... View more

I need help in find logic for this   Parentid  Account_Name  JAN_LY to DEC_LY   JAN_CY  to DEC_CY    Condition  Total_Values 101            MC donalds                                                                            JAN_CY 103             Burger King                                                                           FEB_CY 105            Dominos                                                                                  AUG_LY   Suppose if I have unique/single parentid, unique account_name for that and values in the column Jan LY to DEC CY. There might be 0 or missing values in any of these columns against the parentid number. Suppose if I have to sum the variable values starting from the values in the condition variable. CY is current year. LY is last year   Like for first row for parent id 101 based on condition I need to sum (JAN_CY - DEC CY) and put that in total values column. For parent id 103 I need to sum (FEB_CY-DEC_CY) and for 105 parentif I need to sm from (AUG_LY--DEC_CY). There may be missing values in these columns.   Sorry due to space constraints I am not able to write all columns and values. should i take condition column in macro variable and add like below example   total_values = sum (&condition_values - &reporting_month)..Reporting month can be from jan to dec 2017..             ... View more

data account; input account $ transactionamount    month $; datalines;   FORD   100                           OCT15 FORD   100                           NOV15 FORD   100                           DEC15 FORD   100                           JAN16 FORD   100                           FEB16 FORD   200                          MAR16 FORD   300                              APR16 FORD   300                              MAY16 FORD   600                            JUN16 FORD   300                           JUL16 FORD   140                          AUG16 FORD    150                           SEP16 FORD    700                           OCT16 FORD   300                           NOV16 FORD   140                           DEC16 FORD    60                           JAN17 FORD    100                           FEB17 FORD    100                           MAR17 FORD   100                           APR17 FORD   .                           MAY17 FORD   .                          JUN17 FORD   .                          JUL17 TOYOTA .(same exercise)   I am stuck in this in one of my project I need help. This one is tough to crack. Suppose I have above sample dataset. It is precisely very similar on this line after I transpose the dataset it look like the above for all the accounts. I want three conditions to be fulfilled. Against every account the data starts from jan 2016 to july 2017.   Condition 1 (Three months consecutive loss) Suppose if there are no transaction for three consecutive months I need to this comment in another variable called Reason "Three months consecutive loss. In the above case it would be next to the month JULY17   Condition 2 (3 months consecutive  decline means from oct16-jan17 there is gradually 50% decline every month ) Lets start with first example Jan2017. It would take 3 months back. JAN17_R= (JAN17 -DEC16)/DEC16                                                                                                           Dec16_R=(DEC16-NOV16/NOV16                                                                                                          Nov16_R= (NOV16-OCT16/OCT16     if jan17_R<=.5 and dec16_R <=.5 and Nov16_R<=.5 then do;   reason = "50% Monthon month Decline" It woud give against the month jan17 the reason "50%MOM Decline   Condition 3 (Four Months consecutive Year on Year decline -75% )   Suppose for Jan17example it would take previous three months Oct16, Nov 16, Dec 16 and compare it with 2015 years which is year month back (YOY)  which will be jan 16, Dec15, Nov15, Oct15 for comparison                                                                                                          JAN17_R= (JAN17 -JAN16)/JAN16                                                                                                           Dec16_R=(DEC16-DEC15/DEC15                                                                                                          Nov16_R= (NOV16-NOV15/NOV15                                                                                                          OCT16_R=(OCT16-OCT15)/OCT15   if jan17_R<=.75 and dec16_R <=.75 and Nov16_R<=.75 and oct16_R <.75 then do; It means if there is 75% decline for 4 consecutive  month on YOY basis. IF tthe above case is correct it would put this comment "75% decline YOY" against the month jan 2017   I want to evaluate and imply these these three condition starting from jan2017 to july 2017 for every account. Previous months are for computational purpose. Please help me with code.           ... View more