Building report using PROC SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Building report using PROC SQL

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


Accepted Solutions
Solution
‎01-17-2017 12:06 PM
Super User
Super User
Posts: 7,392

Re: Building report using PROC SQL

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

Re: Building report using PROC SQL

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.

Occasional Contributor
Posts: 9

Re: Building report using PROC SQL

[ Edited ]

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.

Super User
Posts: 10,483

Re: Building report using PROC SQL

[ Edited ]

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?

Frequent Contributor
Frequent Contributor
Posts: 89

Re: Building report using PROC SQL

[ Edited ]

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.

 

 

Occasional Contributor
Posts: 9

Re: Building report using PROC SQL

 

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

 

 

 

 

Frequent Contributor
Frequent Contributor
Posts: 89

Re: Building report using PROC SQL

Where do tran_code_other and trn_date come from?
How about a complete SQL statement? How about some sample data?
Respected Advisor
Posts: 4,641

Re: Building report using PROC SQL

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;
PG
Occasional Contributor
Posts: 9

Re: Building report using PROC SQL

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.

Solution
‎01-17-2017 12:06 PM
Super User
Super User
Posts: 7,392

Re: Building report using PROC SQL

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.

Occasional Contributor
Posts: 9

Re: Building report using PROC SQL

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.

Super User
Super User
Posts: 7,392

Re: Building report using PROC SQL

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.

Valued Guide
Posts: 2,174

Re: Building report using PROC SQL

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.


☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 336 views
  • 0 likes
  • 6 in conversation