BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

What is the difference betwee the two queries:

create table _master
as
                 select
                        a.ACCT_NO
                        ,max(a.ACCT_FIRST_NM) as ACCT_FIRST_NM
                        ,max(a.ACCT_MID_NM) as ACCT_MID_NM
                        ,max(a.ACCT_LAST_NM) as ACCT_LAST_NM
                        ,max(case when b.CUST_NAT_ID_CD ne ' '  then catx('-',d.iso_cntry_cd,b.CUST_NAT_ID_CD) else ' ' end) as CUST_NAT_ID
                        ,max(a.BIRTH_DT) as BIRTH_DT
      ,max(a.CUST_ID) as a_amex
      ,max(a.MKT_ID_cd) as MKT_ID_cd

from outdata1.CRPS311 a left join outdata1.crps311_canada b on a.acct_no=b.acct_no
left join outdata1.isocodes d on b.mkt_id_cd=d.mkt_id_cd
where a.ACCT_FIRST_NM ne '' or a.ACCT_MID_NM ne  '' or a.ACCT_LAST_NM ne ''
group by a.ACCT_NO

create table _mas
as
                 select
                        a.ACCT_NO
                       ,max(case when a.ACCT_FIRST_NM ne '' then a.acct_first_nm end) as ACCT_FIRST_NM
                      ,max(case when a.ACCT_MID_NM ne '' then a.ACCT_MID_NM end) as ACCT_MID_NM
                      ,max(case when a.ACCT_LAST_NM ne '' then a.ACCT_LAST_NM end) as ACCT_LAST_NM
                        ,max(case when b.CUST_NAT_ID_CD ne ' '  then catx('-',d.iso_cntry_cd,b.CUST_NAT_ID_CD) else ' ' end) as CUST_NAT_ID
                        ,max(a.BIRTH_DT) as BIRTH_DT
      ,max(a.CUST_ID) as a_amex
      ,max(a.MKT_ID_cd) as MKT_ID_cd

from outdata1.CRPS311 a left join outdata1.crps311_canada b on a.acct_no=b.acct_no
left join outdata1.isocodes d on b.mkt_id_cd=d.mkt_id_cd
group by a.ACCT_NO

3 REPLIES 3
LinusH
Tourmaline | Level 20

First, quite odd to do max() on names...?

Since there are else in the case, the result would be the same as if you omit the case (since if name is " " won't be true for the then, and the expression will result in " " anyway).

And yes, I think that the result of two queries can differ.

The where clause will filter out accounts that doesn't have any name, the second one will return all accounts, independent on the existence of any names.

What is the purpose?

Data never sleeps
SASPhile
Quartz | Level 8

The purpose is to get a single row for each acct_no

LinusH
Tourmaline | Level 20

Then don't use case and where.

Data never sleeps

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
  • 759 views
  • 0 likes
  • 2 in conversation