I have two datasets that I need both to be merged in my result.  DataSet A has roughly 40K rows and DataSet B has 139K rows. Both has two common columns acct_num and acct_id. When I sort the datasets with acct_num and merge them with acct_num, I get the mergedataset with 139K rows skipping ALL of the data from dataset A . Ideally my merge dataset should have 144343 observations and 35 variables.  I tried reading the other post with same query but I am not able to implement the solution.   I also tried sorting on the acct_id but the mergedataset is not helping. It would be really a learning if I can get to know the ways to resolve it.  Appreciate your time and suggestions. Pasted the warnings from Sas.  My code is error free.            NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. NOTE: MERGE statement has more than one data set with repeats of BY values. NOTE: There were 39585 observations read from the data set MYORA.SETA. NOTE: There were 138977 observations read from the data set MYORA.SETB. NOTE: The data set MYORA.MERGEDATA has 138977 observations and 35 variables. NOTE: DATA statement used (Total process time): real time 7.22 seconds cpu time 3.96 seconds NOTE: Remote submit to ACXIOM complete. ... View more

Hi, I have a date dataset that assigns start date and end date based on the weekday, excluding weekend. Using  the date function I want to calculate the data on 3 days of the date window. Right now the code doesn't evaluate if the data is present on all 3 days, it just pulls whatever data is available even if its on the start date or on the end date or on both. Should introducing middle day solve the issue ? Any insights would be helpful!  data myora.temp_dates; wd = weekday(today()); if wd = 2 then end_date = today() - 3; else end_date = today() - 1; if wd in (2,3,4) then start_date = today() - 5; else start_date = today() - 3; format start_date end_date date9.; run; select id, num, name, COUNT(*) AS name_count, t.start_date, t.end_date, from initial_data1 cross join temp_dates t where auth_dt >= t.start_date and auth_dt <= t.end_date and TO_CHAR(auth_dt, 'Dy') NOT IN ('Sat', 'Sun') group by id,num,name,t.start_date, t.end_date having count(*) >= 10 and sum(amt) >= 100     ... View more

I have an intial_data1 dataset that has my base data; I am trying to get last 3 weekday of data for every weekday between monday to friday for the whole month (the code runs on weekdays). I have determined the logic and wrote a macro for calculating the previous 3 weekdays. I want to display the start and end date for my logic; I have called out the columns I want including the macro variable but I have the 'apparent symbolic reference' not resolved error. %macro weekdays(start_date, end_date); %let current_day = %sysfunc(today()); %let weekday = %sysfunc(weekday(&current_day.)); %if &weekday. = 2 %then %do; /* If it's Monday, fetch wednesday & Friday */ %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.); %let end_date = %sysfunc(intnx(day, &current_day., -3), date9.); %end; %else %if &weekday. = 3 %then %do; /* If it's Tuesday, fetch Thursday & Monday */ %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.); %let end_date = %sysfunc(intnx(day, &current_day., -1), date9.); %end; %else %if &weekday. = 4 %then %do; /* If it's Wednesday, fetch Friday & Monday */ %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.); %let end_date = %sysfunc(intnx(day, &current_day., -1), date9.); %end; %else %if &weekday. = 5 %then %do; /* If it's Thursday, fetch Monday & Wednesday */ %let start_date = %sysfunc(intnx(day, &current_day., -3), date9.); %let end_date = %sysfunc(intnx(day, &current_day., -1), date9.); %end; %else %if &weekday. = 5 %then %do; /* If it's Friday, fetch Tuesday & Thursday */ %let start_date = %sysfunc(intnx(day, &current_day., -3), date9.); %let end_date = %sysfunc(intnx(day, &current_day., -1), date9.); %end; %mend weekdays; %put &start_date.; %put &end_date.; /*calculating last 3 weekdays based on logic*/ %weekdays(&start_date., &end_date.); rsubmit; proc delete data =myora.final_data;run; proc sql; connect to oracle(&ora_str.); create table myora.final_data as select * from connection to oracle( select acct_num, mh_name, mh_cd, auth_dt, count(*) as total_auth_cnts, sum(auth_amt) as sum_auth_amt, "&start_date."d as m_start_date, "&end_date."d as m_end_date from initial_data1 where auth_dt >= TRUNC(SYSDATE) - 10 and auth_dt <= TRUNC(SYSDATE) group by acct_num, mh_name, mh_cd, auth_dt having count(*) >= 10 and sum(auth_amt) >= 100 ); disconnect from oracle; quit; endrsubmit; %put m_start_date: &start_date.; %put m_end_date: &end_date.; Error: 321 proc sql; 322 connect to oracle(&ora_str.); 323 create table myora.final_data as 324 select * from connection to oracle( 325 326 select acct_num, mh_name, mh_cd, auth_dt, 327 count(*) as total_auth_cnts, sum(auth_amt) as sum_auth_amt, 331 "&start_date."d as m_start_date, WARNING: Apparent symbolic reference START_DATE not resolved. 332 "&end_date."d as m_end_date WARNING: Apparent symbolic reference END_DATE not resolved. 333 from initial_data1 334 where auth_dt >= TRUNC(SYSDATE) - 10 and auth_dt <= TRUNC(SYSDATE) 335 group by acct_num, mh_name, mh_cd, auth_dt 336 having 337 count(*) >= 10 338 and sum(auth_amt) >= 100 339 340 ); ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: select acct_num, mh_name, mh_cd, auth_dt, count(*) as total_auth_cnts, sum(auth_amt) as sum_auth_amt, , "&start_date."d as m_start_date, "&end_date."d as m_end_date from initial_data1 where auth_dt >= TRUNC(SYSDATE) - 10 and auth_dt <= TRUNC(SYSDATE) group by acct_num, mh_name, mh_cd, auth_dt having count(*) >= 10 and sum(auth_amt) >= 100. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 341 disconnect from oracle; NOTE: Statement not executed due to NOEXEC option. 342 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.34 seconds cpu time 0.14 seconds NOTE: Remote submit to ACXIOM complete. 556 557 %put m_start_date: &start_date.; m_start_date: 25JUL2024 558 %put m_end_date: &end_date.; m_end_date: 27JUL2024 ... View more