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
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.
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.
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.
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?
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.
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
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;
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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.