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 want to output "Y" if FIN_CD equals "1S". If there's any other variable other than 1S then I do not want to output them. I'm not sure if I'm able to do this in the WHERE statement or if I need a data step.

 

Here is some sample code:

 

PROC SQL;
create table automation_exception as

SELECT
TRANSACTION_FACT.TRN_CD as FIN_CD,
FROM RDWP0.TRANSACTION_FACT, RDWP0.TRANSACTION_CD_LOOKUP, WHERE (TRANSACTION_CD_LOOKUP.TRN_CD=TRANSACTION_FACT.TRN_CD) AND (ACCT_DIM.ACCT_ID=TRANSACTION_FACT.ACCT_ID);

QUIT;
1 ACCEPTED SOLUTION

Accepted Solutions
DanielSantos
Barite | Level 11

Hi.

 

 

Could it be as simple as the following?

 

 


SELECT  
  case when TRANSACTION_FACT.TRN_CD eq '1S' then '1S' else 'N' end as FIN_CD,
       
  

 

Daniel Santos @ www.cgd.pt

 

View solution in original post

3 REPLIES 3
ballardw
Super User

@jmoore168 wrote:

Hi

 

I want to output "Y" if FIN_CD equals "1S". If there's any other variable other than 1S then I do not want to output them. I'm not sure if I'm able to do this in the WHERE statement or if I need a data step.

 


Did you mean if FIN_CD is not equal to '1S' you don't want them in the output at all? If so subset the data as early as practical:

 

FROM

(select * from RDWP0.TRANSACTION_FACT where TRN_CD = '1S') ,

RDWP0.TRANSACTION_CD_LOOKUP,

 

will only select the records of interest before combining with the other data set.

jmoore168
Obsidian | Level 7
Hi Ballardw

Your solution worked perfectly. However I may have been mistaken with regard to how I want to handle other FIN_CD's separately from 1S. For example if FIN_CD output's anything other than '1S' then I want it to output blank or 'N'. This blank tells us we need to take a specific action. 1S means we have already did it. Basically this is an exception report. I need to combine the previous code you sugguested in addition to what I'm asking here.

Hope this helps.
DanielSantos
Barite | Level 11

Hi.

 

 

Could it be as simple as the following?

 

 


SELECT  
  case when TRANSACTION_FACT.TRN_CD eq '1S' then '1S' else 'N' end as FIN_CD,
       
  

 

Daniel Santos @ www.cgd.pt

 

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
  • 3 replies
  • 762 views
  • 1 like
  • 3 in conversation