hi there,   I want to join 2 oracle tables using pass through but i keep mucking up the syntax... this is what i have . What I am doing wrong ?   PROC SQL;  CONNECT TO ORACLE (USER=&NAME  PW=&PASS  PATH=EX);  CREATE TABLE ICO AS   SELECT A.*,B.* FROM CONNECTION TO ORACLE   (SELECT BAN,INDICATOR,BL_IND    FROM  ODS.BILLING_ACCOUNT    WHERE STATUS IN ('O','S') AND ACCOUNT_TYPE||ACCOUNT_SUB_TYPE = 'IR')     A   LEFT JOIN   (SELECT SUB_NO,SUB_STATUS,PRODUCT_TYPE   FROM ODS.SUB B   WHERE SUB_STATUS =  'A'      ON A.BAN = B.CUSTOMER_ID  ;  DISCONNECT FROM ORACLE; QUIT; ... View more

Hi,   I am trying to calculate cumulative total using proc sql. I know how to do in a data step but i am trying to understand sas better and wanted to try using proc sql   data is this. I want a cumulative column for the monthly_pmt variable by ID. I am just testing this code for one ID right now ID ENT_SEQ_NO ACTV_DATE Monthly_pmt month count 759966427 988522250 07JUL2019:00:00:00 150 7 1 759966427 988522251 02AUG2019:00:00:00 150 8 2 759966427 988522252 06SEP2019:00:00:00 150 9 3 759966427 988522253 05OCT2019:00:00:00 100 10 4 759966427 988522254 08NOV2019:00:00:00 400 11 5 759966427 988522256 21DEC2019:00:00:00 200 12 6   I wrote this but i am getting duplication   PROC SQL; CREATE TABLE CUMULATIVE AS SELECT a.*, SUM(b.MONTHLY_PMT) AS CUML FROM TEST3 A INNER JOIN TEST3 B ON A.ID= B.ID and b.count <= a.count GROUP BY A.ID,a.ent_seq_no,a.actv_date ORDER BY A.ID,a.ent_seq_no,a.actv_date ; QUIT;   result is:   ID ENT_SEQ_NO ACTV_DATE Monthly_pmt month count CUML 759966427 988522250 07JUL2019:00:00:00 150 7 1 150 759966427 988522251 02AUG2019:00:00:00 150 8 2 300 759966427 988522251 02AUG2019:00:00:00 150 8 2 300 759966427 988522252 06SEP2019:00:00:00 150 9 3 450 759966427 988522252 06SEP2019:00:00:00 150 9 3 450 759966427 988522252 06SEP2019:00:00:00 150 9 3 450 759966427 988522253 05OCT2019:00:00:00 100 10 4 550 759966427 988522253 05OCT2019:00:00:00 100 10 4 550 759966427 988522253 05OCT2019:00:00:00 100 10 4 550 759966427 988522253 05OCT2019:00:00:00 100 10 4 550 759966427 988522254 08NOV2019:00:00:00 400 11 5 950 759966427 988522254 08NOV2019:00:00:00 400 11 5 950 759966427 988522254 08NOV2019:00:00:00 400 11 5 950 759966427 988522254 08NOV2019:00:00:00 400 11 5 950 759966427 988522254 08NOV2019:00:00:00 400 11 5 950 759966427 988522256 21DEC2019:00:00:00 200 12 6 1150 759966427 988522256 21DEC2019:00:00:00 200 12 6 1150 759966427 988522256 21DEC2019:00:00:00 200 12 6 1150 759966427 988522256 21DEC2019:00:00:00 200 12 6 1150 759966427 988522256 21DEC2019:00:00:00 200 12 6 1150 759966427 988522256 21DEC2019:00:00:00 200 12 6 1150     I got around it by modifying to ask for distinct a.* in the above query but is this the only way? why is it doing this?     ... View more

hi all,    can't seem to figure this out. I have a table  A of ID's and ID_Date and I need to extract records from table B  by matching ID where ID_Date >= Table B_Date date in table A is in date format and date in table b is in datetime format proc sql;  CONNECT TO ORACLE (user=&name  pw=&pass  path=Exa);  create table P_1 as   select * from connection to oracle   (select ID,SEQ_NO,Tableb_DATE,AMT    from  p_act        )     ( WHERE ID IN (SELECT  ID FROM table a)  and ID_date > tableb_Date )     ;  disconnect from oracle; quit;   I keep getting an error. not sure how to go about this... ... View more

This maybe overly simple but I am stuck...   My data is like this: ID Series S_Days P_days 1 1 1_30 150_200 2 5 1_30 150_200 3 5 1_30 150_200 4 1 91_120 NP 5 5 61_90 NP 6 5 61_90 0_30   I need to count the number of ID's where P_days = NP and does not equal NP and group it by Series and S Days   I wrote the following but it's not working...    proc sql; create table test as select Series ,s_days (case when p_days = 'NP' then count(ban) end) as NP, (case when p_days ^= 'NP' then count(ban) end) as Pay from Master group by Series, S_days ; quit; would really appreciate any suggestions. Thank you. ... View more

Hi, Is there a way to pick a specific date of the given week depending on the date. If I have records as: Name Date AD Sept 11 DF Sept 16 I want to pick be able to pick the Wednesday of the week and report it. Output would look like: Name   Date       Week_date AD       Sept 12        Sep 11 DF        Sept 16        Sep 18 I know i can just define the ranges in a data step but I wanted to know if there way a different way to do this... The week would be Sun - Sat Thanks ... View more

For a month i need to pull a summary view like this. I need to pull this month over month and join the counts for each month (t01_2017) to this original table...   BL_CONS_IND INDICATOR COL_NQ_STATUS t01_2017   F N 2431 N F D 218472 N F N 1600066 N B D 297352 N B N 1741985 Y F D 10 Y F N 40 Y R D 186061 Y R N 1331190   This is what i have put together but  I am not sure if this is the right approach and i don't know how to proceed further...   libname d2017 "/sasdatacredit/data/drssblr0717/Historical_Data/AB/&Year."; %MACRO EXTRACT_ELIG(month=,Year=,day=);   libname d2017 "/sasdatacredit/data/drssblr0717/Historical_Data/AB/&Year."; PROC SQL; CREATE TABLE DEL_&month._&year. AS SELECT BL_CONS_IND,INDICATOR,COL_INQ_STATUS,COUNT(COL_INQ_STATUS) as t&month._&year. FROM D2017.AB_&Year.&month.&day. WHERE A_TYPE||A_SUB_TYPE = 'IR' AND STATUS IN ('O','S') AND VE = 'Y' GROUP BY BL_CONS_IND,INDICATOR,COL_INQ_STATUS ; QUIT; %MEND; %EXTRACT_ELIG(month=01,Year=2017,day=31); %EXTRACT_ELIG(month=02,Year=2017,day=28); %EXTRACT_ELIG(month=03,Year=2017,day=31); %EXTRACT_ELIG(month=04,Year=2017,day=30); %EXTRACT_ELIG(month=05,Year=2017,day=31); %EXTRACT_ELIG(month=06,Year=2017,day=30); %EXTRACT_ELIG(month=07,Year=2017,day=31);   With this i can run all the months fairly quick... however i want to create a single table with the counts joined by month... not sure how to achieve that... any ideas? Not experienced with macros... ... View more