Insert condition into proc sql create table

[jeremy4]

Hi,

I have a proc sql table that I have created but can anyone help me out with code (and where to insert it) so that missing values/not 'Yes' for the variable 'letter' in the created table VOLUMES (all of the accounts in b.letter have 'Yes' and when I left join, I want the records in the VOLUMES table that do not get assigned 'Yes' to be assigned 'No') are changed to a No i.e. (the condition I want to include is that if letter = . or letter in the created VOLUMES table is not equal to 'Yes', then letter = 'No')? Thanks!

Would there be code so it creates an output similar to an example I've created below?

In ac.letters as a:

-There is no letter variable but when I left join rb.account_information as b, I would like the records that are not matched by account ID to have 'No' assigned in the newly created VOLUMES table.

In rb.account_information as b:

-All of the records are assigned 'Yes' for the letter variable and this dataset will be left joined onto the ac.letters dataset to match the account ID and therefore give the matched accounts a 'Yes' answer for the letter variable.

Code so far:

proc sql;
    create table VOLUMES as
      select a.account_id,
                a.date,
                b.letter
    from ac.letters as a
   left join rb.account_information as b
    on a.account_id = b.ACCOUNT_ID
   ;
quit;

[Sathish_jammy]

Could you please explain with any sample dataset/table. 

[jeremy4]

Hi,

I can't provide any samples because of confidentiality issues but I have included more information in my description above, if that helps?

[ChrisNZ]

like this?

select a.ACCOUNT_ID
     , a.DATE
     , ifc(b.LETTER='Yes', 'Yes', 'No') as LETTER length=3

[jeremy4]

Hi Chris,

Thanks for your reply. I've added in a diagram to help with the explanation but all of the records in dataset b (rb.account_information) all have been assigned 'Yes' for the letter variable. When I left join to dataset a, I would like all of the matched account ID accounts to therefore have the 'Yes' answer for the letter variable in the proc sql VOLUMES table.

When the account ID's between the two datasets do not match (i.e. could the code wait until the VOLUMES table is created and then assign a 'No' to the unmatched account ID's, instead of putting code for 'No' from dataset b, as this would not help since all of the records in dataset b have been assigned 'Yes')? Thanks!

[ChrisNZ]

Like this?

select a.ACCOUNT_ID
     , a.DATE
     , ifc(a.ACCOUNT_ID is null, 'No', 'Yes') as LETTER length=3

Also, you seem to want a full join rather than a left join?

[jeremy4]

Hi,

I've been asked to do a left join because I would like to keep all of the contents of dataset a (the diagram above was just a small example but there are thousands of records in my dataset) and when dataset b is left joined onto dataset a to assign 'Yes' when the account ID's match, I would like the remaining unmatched ID's from the created VOLUMES table to then be assigned 'No' for the letter variable.

[jeremy4]

I've just used your code but it has put all of the records in the VOLUMES table as having 'Yes' in the letter variable, but from using my initial code, I think there should be a mixture of 'Yes' and 'No' answers as not all account ID's match between the two datasets. Is there any way for the code to be edited so that 'No' is applied only after the VOLUMES table has been created and then when letter is not 'Yes', then it will update the table so that the other answers are 'No'? Thanks!

proc sql;
create table VOLUMES as
select ifc(a.ACCOUNT_ID is null, 'No', 'Yes') as LETTER length=3,
a.date,
b.letter
from ac.letters as a
left join rb.account_information as b
on a.account_id = b.ACCOUNT_ID
;
quit;

[ChrisNZ]

None of my proposals work?

Please provide sample data and expected output for that exact data.

[jeremy4 · Accepted solution]

Hi, I used your ifc concept because that was correct and changed it so that it was when letter is null (i.e. mix and match of both of your suggestions) and it's worked, thanks a lot for your help!

Correct code to use:
proc sql;
   create table VOLUMES as
      select a.account_id,
                a.date,
                ifc(b.letter is null, 'No', 'Yes') as letter length=3
    from ac.letters as a
   left join rb.account_information as b
   on a.account_id = b.ACCOUNT_ID
   ;
quit;