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

Hi All, I am trying to create a dates table for every 3 months, when i am using the below code within Rsubmit the datesqtr datasets is populating as expected. but when i use the same code outside Rsubmit the datesqtr dataset is not populating as expected. Could you please suggest? DATA _NULL_; YR_START_DATE=INTNX('MONTH', DATE(), -1, 'B'); YR_END_DATE=INTNX('MONTH', YR_START_DATE, -12); DO UNTIL(YR_END_DATE>YR_START_DATE); i+1; MNTH_START_DATE=YR_START_DATE; MNTH_END_DATE=INTNX('MONTH', MNTH_START_DATE, 0, 'e'); PREVDTE=INTNX('MONTH', MNTH_START_DATE, -1, 'S'); /*PREVIOUS MONTHS DATE*/ PUT MNTH_START_DATE=YYMMDD10. MNTH_END_DATE=YYMMDD10. PREVDTE=YYMMDD10.; CALL SYMPUT(CATS('MNTH', I), PUT(MNTH_START_DATE, YYMMN6.)); CALL SYMPUT(CATS('MNTHSTDT', I), PUT(MNTH_START_DATE, YYMMN6.)); /*start date current month i.e. reporting period month t-1 month*/ CALL SYMPUT(CATS('MNTHENDDT', I), PUT(MNTH_END_DATE, YYMMN6.)); /*end date current month i.e. reporting period month t-1 month*/ /*FIRST MONTHS DATE YYMM AS TRANSACTION DATE*/ CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); /*atm transaction for the reporting month month*/ /*PREVIOUS MONTH DATE YYMM AS TRANSACTION DATE*/ CALL SYMPUT(CATS('PREVDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -1, 'S'), YYMMDD10.)); /*previous month date*/ CALL SYMPUT(CATS('BGNDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -3, 'S'), YYMMDD10.)); /*match to fetch the qtrly start date from the source format*/ CALL SYMPUT(CATS('ENDDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -2, 'E'), YYMMDD10.)); /*match to fetch the end qtrly date from the source format*/ CALL SYMPUT(CATS('DATE', I), PUT(MNTH_START_DATE, YYMMN6.)); /*transaction for previous months*/ CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); /*atm transaction for previous months*/ /*PREVIOUS MONTH DATE YYMMDD AS TRANSACTION DATE*/ YR_START_DATE=INTNX('MONTH', YR_START_DATE, -1); END; PUT YR_START_DATE YR_END_DATE; RUN; %macro test; %do i=1 %to 4; %let stdte&i. = %sysfunc(intnx(month, %sysfunc(today()), -&i.), 6.); %put stdte&i. = &&stdte&i.; %let endte&i. = %eval(%sysfunc(intnx(month, &&stdte&i., 3), 6.)-1); %put endte&i. = &&endte&i.; proc sql; connect to teradata (user="&myname.@xxxx" pass=&mypwd. tdpid=&id. mode=teradata); create table datesqtr as select * from connection to teradata (select date_key, cldte_sas from xxxx.ss_dte_dim where cldte_sas between &&stdte&i. - 4 and &&endte&i. order by cldte_sas; ) order by cldte_sas; disconnect from teradata; quit; %end; %mend; %test;     ... View more

Hi All, I am trying to create SAS datasets for every month falling under a quarter using a do loop, I am sure its something to do with my do loop which is creating multiple datasets for the same month. please suggest this.  DATA _NULL_; YR_START_DATE=INTNX('MONTH', DATE(), -1, 'B'); YR_END_DATE=INTNX('MONTH', YR_START_DATE, -12); DO UNTIL(YR_END_DATE>YR_START_DATE); i+1; MNTH_START_DATE=YR_START_DATE; MNTH_END_DATE=INTNX('MONTH', MNTH_START_DATE, 0, 'e'); PREVDTE=INTNX('MONTH', MNTH_START_DATE, -1, 'S'); /*PREVIOUS MONTHS DATE*/ PUT MNTH_START_DATE=YYMMDD10. MNTH_END_DATE=YYMMDD10. PREVDTE=YYMMDD10.; CALL SYMPUT(CATS('MNTH', I), PUT(MNTH_START_DATE, YYMMN6.)); CALL SYMPUT(CATS('MNTHSTDT', I), PUT(MNTH_START_DATE, YYMMN6.)); /*start date current month i.e. reporting period month t-1 month*/ CALL SYMPUT(CATS('MNTHENDDT', I), PUT(MNTH_END_DATE, YYMMN6.)); /*end date current month i.e. reporting period month t-1 month*/ /*FIRST MONTHS DATE YYMM AS TRANSACTION DATE*/ CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); /*atm transaction for the reporting month month*/ /*PREVIOUS MONTH DATE YYMM AS TRANSACTION DATE*/ CALL SYMPUT(CATS('PREVDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -1, 'S'),YYMMDD10.)); /*previous month date*/ CALL SYMPUT(CATS('BGNDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -3, 'S'),YYMMDD10.)); /*match to fetch the qtrly start date from the source format*/ CALL SYMPUT(CATS('ENDDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -2, 'E'),YYMMDD10.)); /*match to fetch the end qtrly date from the source format*/ CALL SYMPUT(CATS('DATE', I), PUT(MNTH_START_DATE, YYMMN6.)); /*transaction for previous months*/ CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); /*atm transaction for previous months*/ /*PREVIOUS MONTH DATE YYMMDD AS TRANSACTION DATE*/ YR_START_DATE=INTNX('MONTH', YR_START_DATE, -1); END; PUT YR_START_DATE YR_END_DATE; RUN; /* setting up date macro variables.*/ %MACRO TEST(); %GLOBAL i j STARTDT MNTHSTRDT MNTHENDT PRDDTE PRDENDDTE MNTHSTDT MNTHENDDT DATE ATMDATE ENDDT; %DO j = 1 %TO 13; %DO i = 3 %TO 13 %BY 3; %PUT ITERATION&i. ====>> &i.; %LET STARTDT&i. = %SYSFUNC(INTNX(MONTH,%SYSFUNC(TODAY()),-&i.),6.); /*match to fetch the data from the source format*/ %PUT STARTDT&i.===>> &&STARTDT&i.; %LET ENDDT&i. = %SYSFUNC(INTNX(MONTH,&&STARTDT&i.,3),6.); /*match to fetch the data from the source format*/ %PUT ENDDT&i.===>> &&ENDDT&i.; %LET MNTHSTRDT&i. = %SYSFUNC(INTNX(MONTH,%SYSFUNC(TODAY()),-&i.,B),YYMMDD10.); /*start date of the quarter */ %PUT MNTHSTRDT&i. ====> &&MNTHSTRDT&i.; %LET MNTHENDT&i. = %SYSFUNC(INTNX(MONTH,&&STARTDT&i.,-3,E),YYMMDD10.); /*END date of the quarter */ %PUT MNTHENDT&i. ====> &&MNTHENDT&i.; %PUT JLOOP ===>> &&STARTDT&i.; %END; %END; %MEND; %TEST; 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 %MACRO TEST(); 70 %GLOBAL i j STARTDT MNTHSTRDT MNTHENDT PRDDTE PRDENDDTE MNTHSTDT MNTHENDDT DATE ATMDATE ENDDT; 71 %DO j = 1 %TO 13; 72 %DO i = 3 %TO 13 %BY 3; 73 %PUT ITERATION&i. ====>> &i.; 74 %LET STARTDT&i. = %SYSFUNC(INTNX(MONTH,%SYSFUNC(TODAY()),-&i.),6.); 75 %PUT STARTDT&i.===>> &&STARTDT&i.; 76 %LET ENDDT&i. = %SYSFUNC(INTNX(MONTH,&&STARTDT&i.,3),6.); 77 %PUT ENDDT&i.===>> &&ENDDT&i.; 78 %LET MNTHSTRDT&i. = %SYSFUNC(INTNX(MONTH,%SYSFUNC(TODAY()),-&i.,B),YYMMDD10.); 79 %PUT MNTHSTRDT&i. ====> &&MNTHSTRDT&i.; 80 %LET MNTHENDT&i. = %SYSFUNC(INTNX(MONTH,&&STARTDT&i.,-3,E),YYMMDD10.); 81 %PUT MNTHENDT&i. ====> &&MNTHENDT&i.; 82 %PUT JLOOP ===>> &&STARTDT&i.; 83 %END; 84 %END; 85 %MEND; 86 %TEST; ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 ITERATION3 ====>> 3 STARTDT3===>> 22797 ENDDT3===>> 22889 MNTHSTRDT3 ====> 2022-06-01 MNTHENDT3 ====> 2022-03-31 JLOOP ===>> 22797 ITERATION6 ====>> 6 STARTDT6===>> 22705 ENDDT6===>> 22797 MNTHSTRDT6 ====> 2022-03-01 MNTHENDT6 ====> 2021-12-31 JLOOP ===>> 22705 ITERATION9 ====>> 9 STARTDT9===>> 22615 ENDDT9===>> 22705 MNTHSTRDT9 ====> 2021-12-01 MNTHENDT9 ====> 2021-09-30 JLOOP ===>> 22615 ITERATION12 ====>> 12 STARTDT12===>> 22524 ENDDT12===>> 22615 MNTHSTRDT12 ====> 2021-09-01 MNTHENDT12 ====> 2021-06-30 JLOOP ===>> 22524 87 88 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 98 ... View more

  Please find the below code and help get the datasets for every 3 months. i have tried many things but i am not able to stop at the third month(i.e. the begin and end date the macro variable to should capture the 3rd month but im creating a new value for every i. i have tried Max (&I.)). In "MY CODE" below i am not able to iterate (i++) when the its the 4th month. for ex : Jan Feb and Mar months. im trying to end the procesing at mar and start or pick a new month from april, may or june. the below Original Code we are only extracting for every 3 months but now i am trying to get for every 3rd month in a year.(q1,q2,q3,and q4). Kindly Suggest!!!! /*ORIGINAL CODE*/ DATA _NULL_; TDY = MDY(MONTH(TODAY()),1,YEAR(TODAY())); ENDATE = TDY - 1; STDATE = MDY(MONTH(ENDATE),1,YEAR(ENDATE)); /*CREATE CURRENT START DATE AND END DATE OF THE MONTHS 1ST & LAST DAY OF THE MONTH*/ CALL SYMPUTX('MNSTRT',PUT(STDATE ,YYMMDD10.)); CALL SYMPUTX('MNENDT',PUT(ENDATE,YYMMDD10.)); CALL SYMPUTX('DATE1',PUT(STDATE,YYMMN6.)); /*PRIOR MONTHS -1 MONTH*/ CURR_DTE_2 = INTNX('MONTH',STDATE,-1,'S'); CALL SYMPUTX ('DATE2',PUT(CURR_DTE_2 ,YYMMN6.)); CALL SYMPUTX ('TXNDATE1',PUT(CURR_DTE_2 ,YYMMN6.)); /*PRIOR MONTHS -2 MONTH*/ CURR_DTE_3 = INTNX('MONTH',STDATE,-2,'S'); CALL SYMPUTX ('DATE3',PUT(CURR_DTE_3 ,YYMMN6.)); CALL SYMPUTX ('TXNDATE2',PUT(CURR_DTE_3 ,YYMMN6.)); CALL SYMPUTX ('QTRNDATE',PUT(CURR_DTE_3 ,YYMMDD10.)); /*THIS WILL END FOR THE QTR*/ /*3 MONTHS PRIOR*/ CURR_DTE_4 = INTNX('MONTH',STDATE,-3,'S'); CALL SYMPUTX ('TXNDATE3',PUT(CURR_DTE_4 ,YYMMN6.)); /*TO CREATE THE NUMBER OF DAYS(SAS DAYS)*/ CALL SYMPUTX('BGDTE',CURR_DTE_3); CALL SYMPUTX('endt',ENDATE); PUT CURR_DTE_4 = YYMMDD10. CURR_DTE_3 = YYMMDD10. CURR_DTE_2 = YYMMDD10. STDATE=YYMMDD10. ENDATE = YYMMDD10.; RUN; %put date1====>> &date1. date2====>> &date2. date3====>> &date3.; %put TXNDATE1====>> &TXNDATE1. TXNDATE2====>> &TXNDATE2. TXNDATE3====>> &TXNDATE3.; /*atmdates*/ %PUT BGNDTE ==>> &BGDTE. ; %PUT endDTE ==>> &endt.; /*-----------------------------------------------------------------------------------*/ /*programmatically obtain dates*/ /*MY CODE*/ /*INITIALLY I WAS PLANNING TO GET ALL THE MONTHS FOR AN YEAR AND BREAK THEM INTO QTRS, BUT I FAILED TO. SO USED THE ORIGINAL CODE ABOVE */ DATA _NULL_; YR_START_DATE=INTNX('MONTH', DATE(), -1, 'B'); YR_END_DATE=INTNX('MONTH', DATE(), -12, 'E'); DO UNTIL(YR_END_DATE>YR_START_DATE); i+1; MNTH_START_DATE=YR_START_DATE; MNTH_END_DATE=INTNX('MONTH', MNTH_START_DATE, 0, 'S'); PREVDTE=INTNX('MONTH', MNTH_START_DATE, -1, 'S'); /*PREVIOUS MONTHS DATE*/ PUT MNTH_START_DATE=YYMMDD10. MNTH_END_DATE=YYMMDD10. PREVDTE=YYMMDD10.; CALL SYMPUT(CATS('MNTH', I), PUT(MNTH_START_DATE, YYMMN6.)); CALL SYMPUT(CATS('MNTHSTDT', I), PUT(MNTH_START_DATE, YYMMN6.)); CALL SYMPUT(CATS('MNTHENDDT', I), PUT(MNTH_END_DATE, YYMMN6.)); CALL SYMPUT(CATS('DATE', I), PUT(MNTH_END_DATE, YYMMN6.)); /*FIRST MONTHS DATE YYMM AS TRANSACTION DATE*/ CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); /*PREVIOUS MONTHS DATE YYMM AS TRANSACTION DATE*/ CALL SYMPUT(CATS('PREVDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -1, 'S'), YYMMDD10.)); /*PREVIOUS MONTHS DATE YYMMDD AS TRANSACTION DATE*/ YR_START_DATE=INTNX('MONTH', YR_START_DATE, -1); END; PUT YR_START_DATE YR_END_DATE; RUN; /* I AM TRYING TO ARRANGE THE DATASETS FOR EVERY QUARTER WHICH CAN BE HELPFUL FOR ANALYSIS AND SPACE */ %MACRO QTRS(); %DO i=1 %TO 3;/*TRYING FOR THE 1ST QTR*/ %LET K = %EVAL(&i.+1); /* TO GET THE PREVIOUS DATES FOR THE PREVIOUS MONTHS */ %LET CURR_DATE_&K. = &&PREVDTE&i.; %PUT &&CURR_DATE_&K.; %LET ATMDATE&i. = %SYSFUNC(INPUTN(&&CURR_DATE_&K., YYMMDD10.), YYMMN6.); %PUT ATM&i.===>> &&ATMDATE&i.; %LET DATE_&K. = %SYSFUNC(INPUTN(&&CURR_DATE_&K., YYMMDD10.), YYMMN6.); %PUT DATE&K.===>> &&dATE_&K.; /* TO GET THE LAST QTR DATE - NOT SURE WHY AM I NOT ABLE TO GET IT */ %IF &i.= %EVAL(%SYSFUNC(MAX(&i.))) %THEN %DO; %PUT &I.; %LET BGNDTE = %SYSFUNC(INPUTN(&&PREVDTE&i., YYMMDD10.), 6.); %PUT BGNDTE ==>> &BGNDTE. ; %LET PDSTART = %SYSFUNC(INPUTN(&&PREVDTE&i., YYMMDD10.), YYMMN6.); %PUT PDSTART===>> &PDSTART.; %END; %END; %MEND; %QTRS; /*LOGS*/ 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 /*programmatically obtain dates*/ 70 /*MY CODE*/ 71 /*INITALLY I WAS PLANNING TO GET ALL THE MONTHS FOR AN YEAR AND BREAK THEM INTO QTRS, BUT I FAILED TO. SO USED ORIGINAL 71 ! CODE ABOVE */ 72 DATA _NULL_; 73 YR_START_DATE=INTNX('MONTH', DATE(), -1, 'B'); 74 YR_END_DATE=INTNX('MONTH', DATE(), -12, 'E'); 75 76 DO UNTIL(YR_END_DATE>YR_START_DATE); 77 i+1; 78 MNTH_START_DATE=YR_START_DATE; 79 MNTH_END_DATE=INTNX('MONTH', MNTH_START_DATE, 0, 'S'); 80 PREVDTE=INTNX('MONTH', MNTH_START_DATE, -1, 'S'); 81 82 /*PREVIOUS MONTHS DATE*/ 83 PUT MNTH_START_DATE=YYMMDD10. MNTH_END_DATE=YYMMDD10. PREVDTE=YYMMDD10.; 84 CALL SYMPUT(CATS('MNTH', I), PUT(MNTH_START_DATE, YYMMN6.)); 85 CALL SYMPUT(CATS('MNTHSTDT', I), PUT(MNTH_START_DATE, YYMMN6.)); 86 CALL SYMPUT(CATS('MNTHENDDT', I), PUT(MNTH_END_DATE, YYMMN6.)); 87 CALL SYMPUT(CATS('DATE', I), PUT(MNTH_END_DATE, YYMMN6.)); 88 89 /*FIRST MONTHS DATE YYMM AS TRANSACTION DATE*/ 90 CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); 91 92 /*PRIVOUS MONTHS DATE YYMM AS TRANSACTION DATE*/ 93 CALL SYMPUT(CATS('PREVDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -1, 'S'), 94 YYMMDD10.)); 95 96 /*PRIVOUS MONTHS DATE YYMMDD AS TRANSACTION DATE*/ 97 YR_START_DATE=INTNX('MONTH', YR_START_DATE, -1); 98 END; 99 PUT YR_START_DATE YR_END_DATE; 100 RUN; MNTH_START_DATE=2022-08-01 MNTH_END_DATE=2022-08-01 PREVDTE=2022-07-01 MNTH_START_DATE=2022-07-01 MNTH_END_DATE=2022-07-01 PREVDTE=2022-06-01 MNTH_START_DATE=2022-06-01 MNTH_END_DATE=2022-06-01 PREVDTE=2022-05-01 MNTH_START_DATE=2022-05-01 MNTH_END_DATE=2022-05-01 PREVDTE=2022-04-01 MNTH_START_DATE=2022-04-01 MNTH_END_DATE=2022-04-01 PREVDTE=2022-03-01 MNTH_START_DATE=2022-03-01 MNTH_END_DATE=2022-03-01 PREVDTE=2022-02-01 MNTH_START_DATE=2022-02-01 MNTH_END_DATE=2022-02-01 PREVDTE=2022-01-01 MNTH_START_DATE=2022-01-01 MNTH_END_DATE=2022-01-01 PREVDTE=2021-12-01 MNTH_START_DATE=2021-12-01 MNTH_END_DATE=2021-12-01 PREVDTE=2021-11-01 MNTH_START_DATE=2021-11-01 MNTH_END_DATE=2021-11-01 PREVDTE=2021-10-01 MNTH_START_DATE=2021-10-01 MNTH_END_DATE=2021-10-01 PREVDTE=2021-09-01 22524 22553 NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 606.50k OS Memory 23716.00k Timestamp 09/10/2022 04:42:54 AM Step Count 277 Switch Count 0 Page Faults 0 Page Reclaims 91 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 101 102 /* I AM TRYING TO ARRANGE THE DATASETS FOR EVERY QUARTER WHICH CAN BE HELPFUL FOR ANALYSIS AND SPACE */ 103 %MACRO QTRS(); 104 %DO i=1 %TO 3;/*TRYING FOR THE 1ST QTR*/ 105 %LET K = %EVAL(&i.+1); 106 /* TO GET THE PREVIOUS DATES FOR THE PREVIOUS MONTHS */ 107 %LET CURR_DATE_&K. = &&PREVDTE&i.; 108 %PUT &&CURR_DATE_&K.; 109 110 %LET ATMDATE&i. = %SYSFUNC(INPUTN(&&CURR_DATE_&K., YYMMDD10.), YYMMN6.); 111 %PUT ATM&i.===>> &&ATMDATE&i.; 112 %LET DATE_&K. = %SYSFUNC(INPUTN(&&CURR_DATE_&K., YYMMDD10.), YYMMN6.); 113 %PUT DATE&K.===>> &&dATE_&K.; 114 115 /* TO GET THE LAST QTR DATE - NOT SURE WHY AM I NOT ABLE TO GET IT */ 116 %IF &i.= %EVAL(%SYSFUNC(MAX(&i.))) %THEN %DO; 117 %PUT &I.; 118 %LET BGNDTE = %SYSFUNC(INPUTN(&&PREVDTE&i., YYMMDD10.), 6.); 119 %PUT BGNDTE ==>> &BGNDTE. ; 120 %LET PDSTART = %SYSFUNC(INPUTN(&&PREVDTE&i., YYMMDD10.), YYMMN6.); 121 %PUT PDSTART===>> &PDSTART.; 122 %END; 123 124 %END; 125 %MEND; 126 127 %QTRS; 2022-07-01 ATM1===>> 202207 DATE2===>> 202207 1 BGNDTE ==>> 22827 PDSTART===>> 202207 2022-06-01 ATM2===>> 202206 DATE3===>> 202206 2 BGNDTE ==>> 22797 PDSTART===>> 202206 2022-05-01 ATM3===>> 202205 DATE4===>> 202205 3 BGNDTE ==>> 22766 PDSTART===>> 202205 128 129 130 131 132 133 /*ORIGINAL CODE*/ 134 135 DATA _NULL_; 136 TDY = MDY(MONTH(TODAY()),1,YEAR(TODAY())); 137 ENDATE = TDY - 1; 138 STDATE = MDY(MONTH(ENDATE),1,YEAR(ENDATE)); 139 /*CREATE CURRENT START DATE AND END DATE OF THE MONTHS 1ST & LAST DAY OF THE MONTH*/ 140 CALL SYMPUTX('MNSTRT',PUT(STDATE ,YYMMDD10.)); 141 CALL SYMPUTX('MNENDT',PUT(ENDATE,YYMMDD10.)); 142 CALL SYMPUTX('DATE1',PUT(STDATE,YYMMN6.)); 143 /*PRIOR MONTHS -1 MONTH*/ 144 CURR_DTE_2 = INTNX('MONTH',STDATE,-1,'S'); 145 CALL SYMPUTX ('DATE2',PUT(CURR_DTE_2 ,YYMMN6.)); 146 CALL SYMPUTX ('TXNDATE1',PUT(CURR_DTE_2 ,YYMMN6.)); 147 148 /*PRIOR MONTHS -2 MONTH*/ 149 CURR_DTE_3 = INTNX('MONTH',STDATE,-2,'S'); 150 CALL SYMPUTX ('DATE3',PUT(CURR_DTE_3 ,YYMMN6.)); 151 CALL SYMPUTX ('TXNDATE2',PUT(CURR_DTE_3 ,YYMMN6.)); 152 CALL SYMPUTX ('QTRNDATE',PUT(CURR_DTE_3 ,YYMMDD10.)); /*THIS WILL END FOR THE QTR*/ 153 154 /*3 MONTHS PRIOR*/ 155 CURR_DTE_4 = INTNX('MONTH',STDATE,-3,'S'); 156 CALL SYMPUTX ('TXNDATE3',PUT(CURR_DTE_4 ,YYMMN6.)); 157 158 /*TO CREATE THE NUMBER OF DAYS(SAS DAYS)*/ 159 CALL SYMPUTX('BGDTE',CURR_DTE_3); 160 CALL SYMPUTX('endt',ENDATE); 161 PUT CURR_DTE_4 = YYMMDD10. CURR_DTE_3 = YYMMDD10. CURR_DTE_2 = YYMMDD10. STDATE=YYMMDD10. ENDATE = YYMMDD10.; 162 RUN; CURR_DTE_4=2022-05-01 CURR_DTE_3=2022-06-01 CURR_DTE_2=2022-07-01 STDATE=2022-08-01 ENDATE=2022-08-31 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 561.25k OS Memory 23716.00k Timestamp 09/10/2022 04:42:54 AM Step Count 278 Switch Count 0 Page Faults 0 Page Reclaims 32 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 163 164 %put date1====>> &date1. date2====>> &date2. date3====>> &date3.; date1====>> 202208 date2====>> 202207 date3====>> 202206 165 %put TXNDATE1====>> &TXNDATE1. TXNDATE2====>> &TXNDATE2. TXNDATE3====>> &TXNDATE3.; /*atmdates*/ TXNDATE1====>> 202207 TXNDATE2====>> 202206 TXNDATE3====>> 202205 166 %PUT BGNDTE ==>> &BGDTE. ; BGNDTE ==>> 22797 167 %PUT endDTE ==>> &endt.; endDTE ==>> 22888 168 169 170 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 180   ... View more

Hi All, I am using PC SAS 9.x and trying to use a SAS libname (Local , Teradata or Oracle) in a SAS remote session. I have tried assigning the Libname within the Rsubmit; I'm still getting the libname not assigned Error: I have SAS access interface to DB2,Oracle,Teradata, PC Files, OFBC, and to MYSQL. RSUBMIT; Libname dbtmp teradata user = myname password = xxxxxx tpid = 'xxxxx' schema = 'aaaaaaa'; Libname Winlib 'local path'; Data winlib.emp; Set tmpdb.employee; Run; EndRsubmit; ... View more