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: 
Santt0sh
‎11-01-2023
  • 164 Posts
  • 25 Likes Given
  • 6 Solutions
  • 7 Likes Received

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

by Lapis Lazuli | Level 10 in SAS Procedures
‎10-31-2023 09:10 PM
‎10-31-2023 09:10 PM
Thank you All!     ... View more

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

by Lapis Lazuli | Level 10 in SAS Procedures
‎10-26-2023 07:40 PM
‎10-26-2023 07:40 PM
Hi,   Thank you for your quick response! I will try this.       ... View more

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

by Lapis Lazuli | Level 10 in SAS Procedures
‎10-26-2023 07:39 PM
‎10-26-2023 07:39 PM
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.       ... View more

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

by Lapis Lazuli | Level 10 in SAS Procedures
‎10-26-2023 01:48 PM
‎10-26-2023 01:48 PM
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!             ... View more

Use of Dates in Macro

by Lapis Lazuli | Level 10 in SAS Programming
‎09-14-2023 10:31 PM
‎09-14-2023 10:31 PM
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();   ... View more

Trying to create Macro variables

by Lapis Lazuli | Level 10 in SAS Programming
‎08-16-2023 09:38 PM
‎08-16-2023 09:38 PM
  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. ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎05-17-2023 06:32 PM
‎05-17-2023 06:32 PM
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 ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎05-17-2023 01:00 PM
‎05-17-2023 01:00 PM
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 ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎10-18-2022 09:42 AM
‎10-18-2022 09:42 AM
Hi Patrick, Thank you for your quick response!! I will modify my code and follow the above. Regards, Santosh ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎10-14-2022 03:30 PM
‎10-14-2022 03:30 PM
Hi Reeza, That's was what I am trying to do. Thank you and I will keep posted ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎10-14-2022 03:25 PM
‎10-14-2022 03:25 PM
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. ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎10-14-2022 03:25 PM
‎10-14-2022 03:25 PM
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 ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎10-14-2022 03:19 PM
‎10-14-2022 03:19 PM
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 ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎10-14-2022 02:49 PM
‎10-14-2022 02:49 PM
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. ... View more

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

by Lapis Lazuli | Level 10 in SAS Programming
‎10-14-2022 02:44 PM
‎10-14-2022 02:44 PM
Hi All,   I am trying to create a report. Here we are dealing with daily data, and millions of sales transactions (i.e. daily, monthly, Qtrly ) We are creating the Temp tables (Volatile tables(Teradata) ) and calculating the required variables and outputting it to SAS (.lst file)   but now need to create a report with some additional formatting and I am aware that we cannot directly refer the local path or my path(d:/qtr/sales/).   Can you please suggest me on this?   Regards. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎11-01-2023 12:31 AM
Likes given to
Likes from
View All
Quick links