BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jmoore168
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Super User

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
Barite | Level 11 HB
Barite | Level 11

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
Obsidian | Level 7

 

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
Barite | Level 11 HB
Barite | Level 11
Where do tran_code_other and trn_date come from?
How about a complete SQL statement? How about some sample data?
PGStats
Opal | Level 21

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
jmoore168
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12
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.


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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