About Santt0sh

Santt0sh · ‎02-02-2024

hi, I am too late for this PROC SORT DATA = your_data; BY Customer_Number Transaction_Amount currency_key;RUN; data alerts; set your_data; format alert_date date9.; /* Check for transactions in the last 7 days with the same amount and currency */ by Customer_Number Transaction_Amount currency_key; retain count; if first.Customer_Number then count = 0; if date_key >= intnx('day', today(), -10) and lag(Transaction_Amount) = Transaction_Amount and lag(currency_key) = currency_key then do; count + 1; if count >= 4 then do; alert_date = today(); output; end; end; else count = 0; drop count; run; but one can try PROC SORT DATA = your_data; BY Customer_Number Transaction_Amount currency_key;RUN; data alerts; set your_data; format alert_date date9.; /* Check for transactions in the last 7 days with the same amount and currency */ by Customer_Number Transaction_Amount currency_key; retain count; if first.Customer_Number then count = 0; if date_key >= intnx('day', today(), -10) and lag(Transaction_Amount) = Transaction_Amount and lag(currency_key) = currency_key then do; count + 1; if count >= 4 then do; alert_date = today(); output; end; end; else count = 0; drop count; run; PROC SORT DATA = your_data; BY Customer_Number Transaction_Amount currency_key;RUN; data alerts; set your_data; format alert_date date9.; /* Check for transactions in the last 7 days with the same amount and currency */ by Customer_Number Transaction_Amount currency_key; retain count; if first.Customer_Number then count = 0; if date_key >= intnx('day', today(), -10) and lag(Transaction_Amount) = Transaction_Amount and lag(currency_key) = currency_key then do; count + 1; if count >= 4 then do; alert_date = today(); output; end; end; else count = 0; drop count; run;

Santt0sh · ‎10-31-2023

Thank you All!

Santt0sh · ‎10-26-2023

Hi, Thank you for your quick response! I will try this.

Santt0sh · ‎10-26-2023

Hi, Thank you for your quick response. The lines are displayed in the block or line 14, if i need to add 20 values then all the values will be in row 14 instead of printed in 14 - 34. But i am trying to get my out put something like this.

Santt0sh · ‎10-26-2023

Hi Experts, I am trying create a proc report, where i want to insert lines in the proc report stating what the report does and also want to print some macro variables created in the program. looking at which the users will be able to find if the status of the sales target for product or regions. I know Footnotes, or Titles can be used but i have lot of products and sales figures for multiple regions or vendors. I tried doing it using the Compute block but was able to print the values in a block. is there a way we can write the lines to the proc report without using a compute block? I tried searching, but was not able to find it. Below is the sample code which i am using from one of the examples from proc report. %let Sales = 1545865454; /*dummy sales figure which i want to print on the proc report but in a line and not a blcok.*/ ods rtf file='c:\temp\tryindent2.rtf'; proc report data=sashelp.class(obs=3) nowd style(lines)={fontfamily=Arial font_size=10pt leftmargin=.25in just=l protectspecialchars=off}; title '2) use TAB with RTF Control String'; column _all_; compute after; line "this is first line"; line "\tab{this is second line}"; line "\tab\tab{this is 3rd line}"; endcomp; %put "sales figure for zzzzzzz region is &sales. on &sysdate9."; %put "sales figure for yuyuiyiuyi region is &sales1. on &sysdate9."; %put "total sales figure for NORTH region is &Tsales1. on &sysdate9."; run; ods rtf close; Kindly suggest!

Santt0sh · ‎09-14-2023

Hi All, I am trying to Automate a code to extract daily data for example "01Aug2023 - 02Aug2023" from teradata. i have the below code which is working, but i have 2 issues , Which i am not able to resolve, kindly suggest. 1. When i run the loop from %DO i = 0 %TO &RUN_DAYS. -1 ; I starts from 2 the dates are calculated as 02Aug2023 instead of 01Aug2023, but when i use %DO i = 0 %TO %EVAL(&RUN_DAYS. -1); the dates are calculated day0 = 01Aug2023 and so on.... 2. I am not able to use the dates while extraction data from teradata table. I am able to use the dates as mentioned below, which would include duplicate data. start date End Date 20230802 20230803 20230803 20230804 20230804 20230805 but was trying to have the dates as mentioned below start date End Date 20230802 20230803 20230804 20230805 20230806 20230807 I can extract the data for each day using the above code, as i don't want make changes to the Already running code in production. Please find the logs and the code attached. DATA _NULL_; C_MTH = STRIP(INTNX('MONTH',TODAY(),-1)); /*REPORTING MONTH IS AUGUST2023 OR ANY MONTH PREVIOUS TO THE CURRENT MONTH*/ CALL SYMPUT (CATS('RMTH'),CATT(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.)))); /*ONLY MONTH NAME FOR REPORTING PURPOSES*/ CALL SYMPUT (CATS('REP_MTH'),CATT(LOWCASE(PUT(INTNX('MONTH',C_MTH,0),MONYY7.))) );/*ONLY MONTH & YEAR FORMAT REPORTING PURPOSES*/ CALL SYMPUT (CATS('ACT_MTH'),CATT(LOWCASE(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.))),STRIP(PUT(INTNX('YEAR',C_MTH,0),YEAR2.)))) ; /*ONLY MONTH & YEAR FORMAT REPORTING PURPOSES*/ CALL SYMPUT (CATS('PERIOD'),PUT(INTNX('MONTH',TODAY(),-1),YYMMDDN6.));/*DATE FORMAT FOR EXTRACTION*/ CALL SYMPUT (('RUN_DAYS'),PUT(DAY(INTNX('MONTH',C_MTH,0,'E')),Z2.)); /*NUMBER OF DAYS OF THE MONTH*/ RUN; %PUT "RMTH &RMTH."; %PUT "REP_MTH &REP_MTH."; %PUT "ACT_MTH &ACT_MTH."; %PUT "PERIOD &PERIOD."; %PUT "RUN_DAYS &RUN_DAYS."; %MACRO DATA_PULL(); %DO i = 0 %TO 2; *%EVAL(&RUN_DAYS. -1); %LET MTH&i. = %SYSFUNC(PUTN(&i.,Z2.)); DATA _NULL_; C_MTH = STRIP(INTNX('MONTH',TODAY(),-1)); CALL SYMPUT (CATS("TDY&i."),STRIP(PUT(INTNX('DAY',(INTNX('MONTH',C_MTH,0)),&i.),YYMMDDN8.))); CALL SYMPUT (CATS("PTDY&i."),STRIP(PUT(INTNX('DAY',(INTNX('MONTH',C_MTH,0)),%EVAL(&i.+1)),YYMMDDN8.))); RUN; %PUT "DAYS&i. = &&TDY&i."; %PUT "PTDAYS&i. = &&PTDY&i."; /*WE ARE EXTRACTING DATA FROM TERADATA SALES TABLE. UISNG BETWEEN START DATE AND END DATE FOR A PERIOD OF EXTRACTION.*/ DATA ST&i.; STDTE = "&&TDY&i."; ENDDTE = "&&PTDY&i."; RUN; %END; %MEND; %DATA_PULL(); DATA _NULL_; C_MTH = STRIP(INTNX('MONTH',TODAY(),-1)); /*REPORTING MONTH IS AUGUST2023 OR ANY MONTH PREVIOUS TO THE CURRENT MONTH*/ CALL SYMPUT (CATS('RMTH'),CATT(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.)))); /*ONLY MONTH NAME FOR REPORTING PURPOSES*/ CALL SYMPUT (CATS('REP_MTH'),CATT(LOWCASE(PUT(INTNX('MONTH',C_MTH,0),MONYY7.))) );/*ONLY MONTH & YEAR FORMAT REPORTING PURPOSES*/ CALL SYMPUT (CATS('ACT_MTH'),CATT(LOWCASE(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.))),STRIP(PUT(INTNX('YEAR',C_MTH,0),YEAR2.)))) ; /*ONLY MONTH & YEAR FORMAT REPORTING PURPOSES*/ CALL SYMPUT (CATS('PERIOD'),PUT(INTNX('MONTH',TODAY(),-1),YYMMDDN6.));/*DATE FORMAT FOR EXTRACTION*/ CALL SYMPUT (('RUN_DAYS'),PUT(DAY(INTNX('MONTH',C_MTH,0,'E')),Z2.)); /*NUMBER OF DAYS OF THE MONTH*/ RUN; %PUT "RMTH &RMTH."; %PUT "REP_MTH &REP_MTH."; %PUT "ACT_MTH &ACT_MTH."; %PUT "PERIOD &PERIOD."; %PUT "RUN_DAYS &RUN_DAYS."; %MACRO DATA_PULL(); %DO i = 0 %TO 2; *%EVAL(&RUN_DAYS. -1); %LET MTH&i. = %SYSFUNC(PUTN(&i.,Z2.)); DATA _NULL_; C_MTH = STRIP(INTNX('MONTH',TODAY(),-1)); CALL SYMPUT (CATS("TDY&i."),STRIP(PUT(INTNX('DAY',(INTNX('MONTH',C_MTH,0)),&i.),YYMMDDN8.))); CALL SYMPUT (CATS("PTDY&i."),STRIP(PUT(INTNX('DAY',(INTNX('MONTH',C_MTH,0)),%EVAL(&i.+1)),YYMMDDN8.))); RUN; %PUT "DAYS&i. = &&TDY&i."; %PUT "PTDAYS&i. = &&PTDY&i."; /*WE ARE EXTRACTING DATA FROM TERADATA SALES TABLE. UISNG BETWEEN START DATE AND END DATE FOR A PERIOD OF EXTRACTION.*/ DATA ST&i.; STDTE = "&&TDY&i."; ENDDTE = "&&PTDY&i."; RUN; %END; %MEND; %DATA_PULL();

Santt0sh · ‎08-16-2023

Hi All, Need your help or suggestion in creating Macro variables. I am trying to automate Variable creation where the Macro variable can be used to in the Report creation macro and i am unable to create the Macro variables and resolve them from the 2 datasets ABC and Sales. I am sure i am missing the fundamental of SAS Macros. The output was expecting for example. C_M7 = C_Mary 666667 but i am getting this. C_M7 = C_Mary data abc; input names $; datalines; john ronald Katie Stephen Mike Steve Mary ; run; data sales; input car ; datalines; 1 2 3 4 44 55 666 666667 ; run; proc sql; select Names into :nme separated by ' ' from abc; quit; %put &nme.; %let ct = %sysfunc(countw(&nme.)); %put ct = &ct.; %macro t; %do i = 1 %to %sysfunc(countw(&nme.)); %let vrnm&i. = %scan(&nme.,&i.); %PUT vrnm&i. = &&vrnm&i. ; DATA _NULL_; SET SALES; CALL SYMPUT (CATS("C_&&VRNM&i."),STRIP(PUT(CAR,8.2))); RUN; %PUT C_M&i. = C_&&VRNM&i.; %end; %mend; %t; Please see the logs attached. Thanks in advance.

Santt0sh · ‎05-17-2023

Hi All, I am trying to create a table db1.trans with columns. %Let Acct = User; Proc sql ; Connect to teradata( user= myuserid password = mypwd tdpid = zzzz@somebank.com mode = Teradata); Execute( Create table db1.Acct_&Acct. As ( Select trandte, &Acct. As AcctType From db1. All_Accts) with data ) by Teradata; Quit; When I try the above code the I get an error states that user column is not present on db1.Accts table. Could you please suggest me to add the macro variable &Acct as column in the new table I’m creating. Thanks

Santt0sh · ‎05-17-2023

Hi All, I am trying to read .Xlsx files from an url (Share point) into SAS as Sas datasets. I have tried file name url but it didn’t work for me. HTTPS:// abcbankone.sharepoint.com/sites/2500-07100/Trn/forms/Allitems.asps?noAuthredirect=1 This location has folders Folder one Transactions Next folder folderA,FolderB ….. Folder All trans Folder 1 Trn 2021 Folder 2 Trn 2022 Folder 3 Trn 2023 And so on… I tried mapping the url to a folder on my desktop but now not able to map it. I have tried using file name url but it didn’t help Thanks

Santt0sh · ‎10-18-2022

Hi Patrick, Thank you for your quick response!! I will modify my code and follow the above. Regards, Santosh

Santt0sh · ‎10-14-2022

Hi Reeza, That's was what I am trying to do. Thank you and I will keep posted

Santt0sh · ‎10-14-2022

Hi Reeza, Thank you for your quick response! Unfortunately, I can't post the Rsubmit logs, Apologies. I have access to Teradata when I try running previously used code where the values are hardcoded for a period every 3 months in the Code i am able to create the required datasets, on my local.

Santt0sh · ‎10-14-2022

Hi, Thank you for your quick response! I tried creating the datasets, as we are dealing with huge sales transactions the creation of datasets is taking longer than anticipated hence trying to do the calculations on the fly. Rgs

Santt0sh · ‎10-14-2022

CLDTE_SAS contains = 22,732 22733 and so on. (SAS Date) SAS LOGS Run local not Rsubmit; 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 DATA _NULL_; 70 YR_START_DATE=INTNX('MONTH', DATE(), -1, 'B'); 71 YR_END_DATE=INTNX('MONTH', YR_START_DATE, -12); 72 73 DO UNTIL(YR_END_DATE>YR_START_DATE); 74 i+1; 75 MNTH_START_DATE=YR_START_DATE; 76 MNTH_END_DATE=INTNX('MONTH', MNTH_START_DATE, 0, 'e'); 77 PREVDTE=INTNX('MONTH', MNTH_START_DATE, -1, 'S'); 78 79 /*PREVIOUS MONTHS DATE*/ 80 PUT MNTH_START_DATE=YYMMDD10. MNTH_END_DATE=YYMMDD10. PREVDTE=YYMMDD10.; 81 CALL SYMPUT(CATS('MNTH', I), PUT(MNTH_START_DATE, YYMMN6.)); 82 CALL SYMPUT(CATS('MNTHSTDT', I), PUT(MNTH_START_DATE, YYMMN6.)); 83 84 /*start date current month i.e. reporting period month t-1 month*/ 85 CALL SYMPUT(CATS('MNTHENDDT', I), PUT(MNTH_END_DATE, YYMMN6.)); 86 87 /*end date current month i.e. reporting period month t-1 month*/ 88 /*FIRST MONTHS DATE YYMM AS TRANSACTION DATE*/ 89 CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); 90 91 /*atm transaction for the reporting month month*/ 92 /*PREVIOUS MONTH DATE YYMM AS TRANSACTION DATE*/ 93 CALL SYMPUT(CATS('PREVDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -1, 'S'), 94 YYMMDD10.)); 95 96 /*previous month date*/ 97 CALL SYMPUT(CATS('BGNDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -3, 'S'), 98 YYMMDD10.)); 99 100 /*match to fetch the qtrly start date from the source format*/ 101 CALL SYMPUT(CATS('ENDDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -2, 'E'), 102 YYMMDD10.)); 103 104 /*match to fetch the end qtrly date from the source format*/ 105 CALL SYMPUT(CATS('DATE', I), PUT(MNTH_START_DATE, YYMMN6.)); 106 107 /*transaction for previous months*/ 108 CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); 109 110 /*atm transaction for previous months*/ 111 /*PREVIOUS MONTH DATE YYMMDD AS TRANSACTION DATE*/ 112 YR_START_DATE=INTNX('MONTH', YR_START_DATE, -1); 113 END; 114 PUT YR_START_DATE YR_END_DATE; 115 RUN; MNTH_START_DATE=2022-09-01 MNTH_END_DATE=2022-09-30 PREVDTE=2022-08-01 MNTH_START_DATE=2022-08-01 MNTH_END_DATE=2022-08-31 PREVDTE=2022-07-01 MNTH_START_DATE=2022-07-01 MNTH_END_DATE=2022-07-31 PREVDTE=2022-06-01 MNTH_START_DATE=2022-06-01 MNTH_END_DATE=2022-06-30 PREVDTE=2022-05-01 MNTH_START_DATE=2022-05-01 MNTH_END_DATE=2022-05-31 PREVDTE=2022-04-01 MNTH_START_DATE=2022-04-01 MNTH_END_DATE=2022-04-30 PREVDTE=2022-03-01 MNTH_START_DATE=2022-03-01 MNTH_END_DATE=2022-03-31 PREVDTE=2022-02-01 MNTH_START_DATE=2022-02-01 MNTH_END_DATE=2022-02-28 PREVDTE=2022-01-01 MNTH_START_DATE=2022-01-01 MNTH_END_DATE=2022-01-31 PREVDTE=2021-12-01 MNTH_START_DATE=2021-12-01 MNTH_END_DATE=2021-12-31 PREVDTE=2021-11-01 MNTH_START_DATE=2021-11-01 MNTH_END_DATE=2021-11-30 PREVDTE=2021-10-01 MNTH_START_DATE=2021-10-01 MNTH_END_DATE=2021-10-31 PREVDTE=2021-09-01 MNTH_START_DATE=2021-09-01 MNTH_END_DATE=2021-09-30 PREVDTE=2021-08-01 22493 22524 NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 647.03k OS Memory 23972.00k Timestamp 10/14/2022 07:17:46 PM Step Count 24 Switch Count 0 Page Faults 0 Page Reclaims 238 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 116 117 %macro test; 118 %do i=1 %to 4; 119 %let stdte&i. = %sysfunc(intnx(month, %sysfunc(today()), -&i.), 6.); 120 %put stdte&i. = &&stdte&i.; 121 %let endte&i. = %eval(%sysfunc(intnx(month, &&stdte&i., 3), 6.)-1); 122 %put endte&i. = &&endte&i.; 123 124 /* proc sql; */ 125 /* connect to teradata (user="&myname.@xxxx" */ 126 /* pass=&mypwd. tdpid=&id. mode=teradata); */ 127 /* create table datesqtr as select * from connection to teradata */ 128 /* (select date_key, cldte_sas from xxxx.ss_dte_dim where cldte_sas */ 129 /* between &&stdte&i. - 4 and &&endte&i. */ 130 /* order by cldte_sas; */ 131 /* ) order by cldte_sas; */ 132 /* disconnect from teradata; */ 133 /* quit; */ 134 135 %end; 136 %mend; 137 138 %test; stdte1 = 22889 endte1 = 22979 stdte2 = 22858 endte2 = 22949 stdte3 = 22827 endte3 = 22918 stdte4 = 22797 endte4 = 22888 139 140 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 150

Santt0sh · ‎10-14-2022

Hi Tom, Thank you for your quick response. I am not sure what's going wrong when i run the code outside rsubmit. 1. What are you rsubmitting? Rsubmit; %macro test; Above Code... %mend; %test; Endrsubmit; The sas code shared works when executted using Rsubmit, and doest work when i tried out side the Rsubmit. I will try to use the link provided Tom.

Online Status	Offline
Date Last Visited	‎02-24-2024 02:19 PM

Re: SAS AML Custom Scenario Coding

Re: Can we write multiple lines in an excel sheet using Proc report?

Re: Can we write multiple lines in an excel sheet using Proc report?

Re: Can we write multiple lines in an excel sheet using Proc report?

Can we write multiple lines in an excel sheet using Proc report?

Use of Dates in Macro

Trying to create Macro variables

Can I create a column xyz in a Teradata table pass thru?

Need to read the .xlsx files from folders in an url

Re: Trying to create a table from Teradata using SAS macros.

Re: How to split a table/dataset

Re: Need to read the .xlsx files from folders in an url

Re: how to download a directory of sas files to pc using sas studio (s...

Re: Split data in to quarters

Re: to export a Proc Report export to excel with formats and into dif...

Re: create a macro variable uisng proc sql in a pass thru facility rs...

Re: Teradata Spool Space error

to export a Proc Report export to excel with formats and into differe...

How do I import files(.xlsx) files from Tableau

To import an Excel file into SAS with column headers on more than one ...

Re: SAS AML Custom Scenario Coding

Re: Can we write multiple lines in an excel sheet using Proc report?

Re: Can we write multiple lines in an excel sheet using Proc report?

Re: Can we write multiple lines in an excel sheet using Proc report?

Can we write multiple lines in an excel sheet using Proc report?

Use of Dates in Macro

Trying to create Macro variables

Can I create a column xyz in a Teradata table pass thru?

Need to read the .xlsx files from folders in an url

Re: Trying to create a table from Teradata using SAS macros.

Re: Trying to create a table from Teradata using SAS macros.

Re: Trying to create a table from Teradata using SAS macros.

Re: Can we write a table to an excel to a local file, when running the...

Re: Trying to create a table from Teradata using SAS macros.

Re: Trying to create a table from Teradata using SAS macros.