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: 
Mchan890
‎05-20-2021
  • 7 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: proc sql WITH

by Calcite | Level 5 in SAS Programming
‎05-18-2021 02:02 PM
‎05-18-2021 02:02 PM
the WITH statement in Oracle is looking for a similar match of (vendor_id, invoice_id, gross_amt) having more than 1 match for potential duplicates   The logic is trying to identify potential match in the data for a set of criteria (vendor_id, invoice_id, gross_amt) ... another would be for (invoice_id, invoice_dt, gross_amt) for example ... View more

proc sql WITH

by Calcite | Level 5 in SAS Programming
‎05-12-2021 04:41 PM
‎05-12-2021 04:41 PM
I have this query below that works in Oracle, however, needed to run in SAS proc sql to join with other data. Does anyone know how to convert this statement to work with proc sql? Also, if someone is familiar with this output, how come with the invoice date range set between April and May 2021, this query still pulls in ALL dates in the ouput?  with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as ( select vendor_id,invoice_id, pay_amt,count(*) from ps_voucher where invoice_dt between '01-Apr-2021' and '31-May-2021' group by vendor_id,invoice_id, pay_amt having count(*)>1) select t.vendor_id, t.voucher_id,t.INVOICE_ID,t.gross_amt, t.INVOICE_DT from ps_voucher t join pay_cte p on t.vendor_id=p.vendor_id and t.invoice_id = p.invoice_id and t.gross_amt=p.pay_amt ... View more

Trim from right and left side by spaces

by Calcite | Level 5 in New SAS User
‎12-17-2020 09:50 AM
‎12-17-2020 09:50 AM
Value TEST - Group HS M228S Level 7  on SUPGRPPRF Value TEST - Group HS M228S Level 6 - LF on SUPGRPPRF Value TEST - Group HS M228S Level 6 - LF on GRP Value TEST - Group HS M228S Level 6 - LF on GRP Value TEST - Group HS M228S Level 6 - LF on GRP Value TEST - Group HS M228S on SSGRP Value TEST - Group HS M228S on SSGRP   I have the sample values above that needs to be trim from the left and right side. For example, I need the return values to be after the 3rd spaces from the left side and 2nd spaces from the right side.   Need return values: Group HS M228S Level 7  Group HS M228S Level 6 - LF Group HS M228S Level 6 - LF  Group HS M228S Level 6 - LF Group HS M228S Level 6 - LF Group HS M228S  Group HS M228S   ... View more

How to remove all rows if duplicate

by Calcite | Level 5 in SAS Programming
‎03-09-2020 04:32 PM
‎03-09-2020 04:32 PM
I have a large data set:   Have: PO   Amount   Line 1212        300     1          1233        250     1         1233        600     2         1345      1520     1         1350      1000     2 1350        500     3   Want: PO   Amount   Line 1212        300     1              1345      1520     1           I know the nodupkey will remove the duplicates, however, I am trying to remove both if there are duplicate.     ... View more

Re: How to identify consecutive dates in SAS

by Calcite | Level 5 in SAS Programming
‎01-30-2020 12:02 PM
‎01-30-2020 12:02 PM
Pretty much I have a dataset of emplid and trans_dt and I need to flag when there is 'changed' to identify consecutive days. I would eventually sum this data by emplid to determine how many consecutive transaction dates an employee have.    EMPLID TRANS_DT CHANGED 300717 12/10/2019 1 300717 12/11/2019 0 300717 12/11/2019 1 300717 12/12/2019 0 300717 12/16/2019 1 300717 12/17/2019 0 300717 12/18/2019 0 300717 12/19/2019 0 300717 12/20/2019 0 300717 12/30/2019 1 300717 12/31/2019 0 300717 1/2/2020 1 300717 1/3/2020 0 300717 1/8/2020 1 300717 1/9/2020 0 300717 1/10/2020 0 300717 1/13/2020 1 300717 1/14/2020 0 300717 1/15/2020 0 300717 1/16/2020 0 300717 1/17/2020 0 300717 1/20/2020 1 300717 1/21/2020 0 300717 1/22/2020 0 300717 1/23/2020 0 300717 1/24/2020 0 ... View more

Re: How to identify consecutive dates in SAS

by Calcite | Level 5 in SAS Programming
‎01-30-2020 10:03 AM
‎01-30-2020 10:03 AM
Thanks for t he feedback! The main data (ps_ex_sheet_hdr, ps_ex_sheet_line) are pulled with Oracle connection in SAS. I needed to convert the query to a Proc SQL on a subset of data in SAS. ... View more

How to identify consecutive dates in SAS

by Calcite | Level 5 in SAS Programming
‎01-30-2020 09:37 AM
‎01-30-2020 09:37 AM
I have the SQL query below that works in Oracle TOAD during testing to identify number of consecutive days for transactions by employee id. However, I need to incorporate this query into SAS to read of a SASdata set. Would someone know how to convert the SQL below to run in SAS? Thanks!   select y.emplid, count(*) as Num_ConsecDays from ( select x.emplid,x.trans_dt, sum(changed) over (partition by x.emplid order by x.trans_dt) sum_changed from ( select a.emplid, b.trans_dt, (case when lag(b.trans_dt) over (partition by a.emplid order by b.trans_dt) - b.trans_dt = -1 then 0 else 1 end ) as changed from ps_ex_sheet_hdr a, ps_ex_sheet_line b where b.trans_dt >= to_date('12/1/2019 00:00:00', 'mm/dd/yyyy hh24:mi:ss') and b.trans_dt <= to_date('1/27/2020 23:59:59', 'mm/dd/yyyy hh24:mi:ss') and a.sheet_id = b.sheet_id and a.emplid = '300717')x )y group by y.emplid, sum_changed; ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎05-20-2021 06:24 PM
Quick links