Building report using PROC SQL

[jmoore168]

Hi!

I've been using SAS for under a year so please bear with me as I articulate the problem I am having.  I'm trying to run a report using PROC SQL and I need to include a field separately on the report.  For example, I need to include a variable called "TQ" and if it is present I then need to include variables "SB", "SD", "SF", etc.  The way I have it coded now includes everything together. 

ACTVT_RSLT_FACT.TRN_CD IN ( 'TQ', 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' )

I need to separate TQ from the rest of the tran codes listed on the report.  I tried this:

ACTVT_RSLT_FACT.TRN_CD LIKE 'TQ'

AND

ACTVT_RSLT_FACT.TRN_CD IN ( 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' )

But of course that didn't work.  I'm building a report that shows TQ followed by SB, SD, SE so then I can calculate the days between them.

I hope all this made sense and someone is able to help. 

Thanks

Justin

[RW9]

Sorry, you mixing up terms here.  SQL is not a report function, it is a query language.  So your querying a dataset to return only those coded values and you want the observations to appear in a certain order which is not the alphabetic order, correct?  If so then you need to apply a format:

proc format;

  value $xyz

  'TQ'=2...

run;

Or alternatively case when 'TQ' then 1... end as ORD_VAR, and order by ORD_VAR.

I.e. so you create a numerical version of the code which can be used to sort them.  If you provide some tests data, in the form of a datastep, can provide code.

[jmoore168]

Hi RW9 thank you for your quick response.  I'm learning the correct terminology already.  Here is some sample code:

WHERE
    (ACCT_DIM.ACCT_ID=ACTVT_RSLT_FACT.ACCT_ID)
    AND  
    ( STATUS_CD_LOOKUP.STATUS_CD=ACCT_DIM.STATUS_CD  )
    AND
    (ACCT_DIM.ACCT_ID=TRANSACTION_FACT.ACCT_ID)
    AND
    ( ATTORNEY_CD_LOOKUP.RECOVERER_CD=ACCT_DIM.ATTORNEY_CD  )
    AND
    ACTVT_RSLT_FACT.TRN_CD IN ( 'TQ', 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' )
    AND
    ACTVT_RSLT_FACT.TRN_DT BETWEEN '1NOV2016'd and TODAY()-1
    AND
    TRANSACTION_FACT.TRN_CD LIKE '1S'
;
run;

I need the report to pull tran code TQ first.  If TQ is found THEN I want to query tran codes 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' along with the tran_dt for TQ and the other codes.

Thanks for your help.

[ballardw]

It really helps to provide a few example records of input data and then show the desired result. You don't need to provide records for all possible values but it does help to have some "matches" and some that don't.

As it is when you say: tran code TQ first.  If TQ is found THEN I want to query tran codes 

it sounds like you are looking at a single variable with a single value. If the trn_cd='TQ' then it can't find any other codes. So are you looking in another variable? Or another record? If so, how do we know which other record.

And without a lot of work SQL does not really have any concept of "first", "last" or "before". Do mean matching some other identifier(s) and the date for the 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV' codes are attached to records with a date greater than for the TQ value?

[HB]

I think I would parse my data some before starting in with the SQL queries.

You ony want records where TRN_CD is TQ?

data TQs_only;
  set initial_file;
  if trn_cd = 'TQ';
run;

Parse things down with a bunch of that WHERE statement.

You might also want to look at an SQL CASE statement.

[jmoore168]

This is how it is now:

ACCT_NUM	TRN_CD	TRN_DT	
**************   TQ	12/16/2016

This is how I want it to display

ACCT_NUM     TRN_CD_TQ   TRN_DT	      Tran_Code_Other	  TRN_DATE	   
************	TQ	  12/16/2016	*OTHER TRN_CODE	  *OTHR TRN_DATE

[HB]

Where do tran_code_other and trn_date come from?
How about a complete SQL statement? How about some sample data?

[PGStats]

Seems like you need to do:

create table acct as
select acct_num, trn_cd, trn_dt
from ....
WHERE
    (ACCT_DIM.ACCT_ID=ACTVT_RSLT_FACT.ACCT_ID)
    AND  
    ( STATUS_CD_LOOKUP.STATUS_CD=ACCT_DIM.STATUS_CD  )
    AND
    (ACCT_DIM.ACCT_ID=TRANSACTION_FACT.ACCT_ID)
    AND
    ( ATTORNEY_CD_LOOKUP.RECOVERER_CD=ACCT_DIM.ATTORNEY_CD  )
    AND
    ACTVT_RSLT_FACT.TRN_CD IN ( 'TQ', 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' )
    AND
    ACTVT_RSLT_FACT.TRN_DT BETWEEN '1NOV2016'd and TODAY()-1
    AND
    TRANSACTION_FACT.TRN_CD LIKE '1S';
select tq.*, x.trn_cd as TranCodeOther, x.trn_dt as trn_date
from 
	acct as tq inner join
	acct as x on tq.acct_num=x.acct_num
where tq.trn_cd = "TQ" and x.trn_cd ne "TQ";
quit;

[jmoore168]

Hi PG Stats

Sorry for the delay as I was out of the office due to the holiday.  I used your code but I received an error message

ERROR: File WORK.ACCT.DATA does not exist.
ERROR: File WORK.ACCT.DATA does not exist.

The query did produce observations but it was the same as before.

ACCT_NUM	TRN_CD	TRN_DT
123456789	SF	        15Dec2016 
123456789	TQ	        16Dec2016

Here's the entire code I used to run it:

PROC SQL;
create table automation_exception as

SELECT
    ACCT_DIM.ACCT_NUM,
    ACTVT_RSLT_FACT.TRN_CD,
    ACTVT_RSLT_FACT.TRN_DT,
    ACTVT_RSLT_FACT.TRN_NOTE,
    ACTVT_RSLT_FACT.USERID,
    STATUS_CD_LOOKUP.STATUS_CD,
    ACCT_DIM.LST_STATUS_CD,
    ACCT_DIM.LST_STATUS_CHG_DT,
    ACCT_DIM.DT_ASSIGNED,
    ACCT_DIM.ATTORNEY_CD,
    ATTORNEY_CD_LOOKUP.RECOVERER_CD_DESC,
    ACCT_DIM.ACCT_PURGED_FLG,
    TRANSACTION_FACT.TRN_CD
       
FROM
    RDWP0.ACCT_DIM,
    RDWP0.ACTVT_RSLT_FACT,
    RDWP0.STATUS_CD_LOOKUP,
    RDWP0.TRANSACTION_FACT,
    RDWP0.RECOVERER_CD_LOOKUP  ATTORNEY_CD_LOOKUP


WHERE
    (ACCT_DIM.ACCT_ID=ACTVT_RSLT_FACT.ACCT_ID)
    AND  
    ( STATUS_CD_LOOKUP.STATUS_CD=ACCT_DIM.STATUS_CD  )
    AND
    (ACCT_DIM.ACCT_ID=TRANSACTION_FACT.ACCT_ID)
    AND
    ( ATTORNEY_CD_LOOKUP.RECOVERER_CD=ACCT_DIM.ATTORNEY_CD  )
    AND
    ACTVT_RSLT_FACT.TRN_CD IN ( 'TQ', 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' )
    AND
    ACTVT_RSLT_FACT.TRN_DT BETWEEN '1NOV2016'd and TODAY()-1
    AND
    TRANSACTION_FACT.TRN_CD LIKE '1S';

  SELECT tq.*, x.trn_cd as TranCodeOther, x.trn_dt as trn_date

  From
         acct as tq inner join
         acct as x on tq.acct_num=x.acct_num
  Where
         tq.trn_cd = "TQ" and x.trn_cd ne "TQ";

;
QUIT;

Thanks for your assistance.

[RW9 · Accepted solution]

Hi,

Something like:

proc sql;
  create table WANT as
  select  A.ACCT_NUM,
          A.TRN_CD,
          B.TRN_CD as TRN_CODE_OTHER,
          B.TRN_DT
  from    (select * from ACCOUNTS where TRN_CD="TQ") A
  left join (select * from ACCOUNTS where TRN_CD ne "TQ") B
  on      A.ACCT_NUM=B.ACCT_NUM;
quit;

Do note for the future, to post a well formed question, start by displaying test data (a few rows, in the form of a datastep) and what the output should look like.  We can't see what your doing or what you have/want without it.

[jmoore168]

Thank you so much RW9.  I was able to separate TQ from the rest of the tran codes.  However what would be the solution to add trn_dt for TQ and the other trn_cd's?

Here it is now:

ACCT_NUM	TRN_CD	TRN_CODE_OTHER	TRN_DT
123456789	  TQ	      SF	    15Dec2016

I would like it to show:

ACCT_NUM	TRN_CD	TRN_CODE_OTHER	TRN_DT		Diff_Between_TQ_and_TRN_CODE_OTHER(TRN_DT)
123456789	  TQ	      SF	    15Dec2016

OR

ACCT_NUM	TRN_CD	TRN_CODE_OTHER	TQ(TRN_DATE)	TRN_CODE_OTHER(TRN_DT)
123456789	  TQ	      SF	    

The second option is fine since I can create a formula in excel to calculate the difference between dates.

[RW9]

Well, SQL is straight forward,  You Select the variables or calculations From the dataset{s} you want, and Join them if there is more than one.  This code:

proc sql;
  create table WANT as
  select  A.ACCT_NUM,
          A.TRN_CD,
          A.TRT_DT,
          B.TRN_CD as TRN_CODE_OTHER,
          B.TRN_DT,
          A.TRN_DT - B.TRN_DT as DIFF_BETWEEN
  from    (select * from ACCOUNTS where TRN_CD="TQ") A
  left join (select * from ACCOUNTS where TRN_CD ne "TQ") B
  on      A.ACCT_NUM=B.ACCT_NUM;
quit;

Will give the diff between.  However, bear in mind that the left join will take all non TQ rows and add them to the TQ row, so you may end up with multiple rows.  If you have this, then either left join based on exact TRN_CD="SF", or transpose your data up - which would be simplest:

/* Assumes sorted */
proc transpose data=have out=tran_dates;
  by acct_num;
  var trn_dt;
  id trn_cd;
  idlabel trn_cd;
run;

This will give you a table something like:

ACCT_NUM    SF                 TQ               UV

0000000001    12JAN2012     01DEC2011  13MAR2011

Whic you can then simlpy process with if statements in a datastep, or use arrays on.

[Peter_C]

This query started talking about transaction codes and now it appears to be about an account.

There probably needs to be an inner join of 2 streams of data selected from the original.
The first stream identifies accounts for which a TQ is present and the second stream is the other transactions (subject to being not too old)
The join is on account number.
The date of the TQ transaction acts like an opening-date.
The output of the join generates multiple rows per account number. However it seems the requested shape is one row per account with columns for each transaction thpe.

It is not obvious whether there might be multiple transaction for each transaction type, nor how to handle that.
If the data are unique by transaction type within each account, a transpose is needed. However for non-unique, deduplication needs to have rules defined.