Getting over partition error.

[Lakshmisukanya]

 Data is coming from two different servers one field is coming from one server and another field is coming from another server. Here ATT  feild is coming from one server and  Hpp field  is coming from another server .Like this  

Proc SQL;

Create table ABC as select * from (

Select distinct

Decode (Max(Case when coalesce(CA.ATT,' ') = 'Test' then 1 else 0 end ) over ( partition by MDM.Hpp),1,'Y','N') as SAM from MACM as Mdm

Left join CCCT as CA

On MDM.ALKP = CA.ALKP

);

Quit;

[Patrick]

Partition clauses are not supported in the SAS SQL flavor.

If you want to use such syntax then you need to use explicit SQL that executes directly in a database where such syntax is valid. And of course the story gets even a bit more complicated with a heterogenous join. 

If you need a more detailed answer then you need to provide more detail first (like which type of servers are you connecting to).

[Lakshmisukanya]

Hadoop and gcp servers

[Kurt_Bremser]

Since you want to combine tables from different external databases, you cannot use explicit pass-through, and must do the operation in SAS with the available SAS tools. Neither PARTITION nor DECODE is available in SAS

Please provide usable examples for the datasets as they appear in SAS, and what you expect to get out of these examples.

[Tom · Accepted solution]

Let's try to translate those functions into English and then perhaps we can translate them back to standard SQL that PROC SQL can support.

This part makes no sense

coalesce(CA.ATT,' ')

That is saying that if CA.ATT is missing then use a blank instead.  But if CA.ATT is missing its value already is blank.

Case when coalesce(CA.ATT,' ') = 'Test' then 1 else 0 end

This is saying that when CA.ATT is 'Test' then the result is 1 otherwise it is 0.  That is exactly what the comparison

CA.ATT = 'Test'

does. Remember that SAS only use BINARY logic unlike the TRI LEVEL logic used by some databases.  All comparison are either TRUE (1) or FALSE (0). Even when one or more of the values is missing.

The MAX() aggregate function is the same.  It takes the maximum over observations.  So the result is TRUE if it is true for ANY of the observations being aggregated.

The DECODE() seems to be translating 1 to 'Y'.  In SAS you would just use a FORMAT to do that.  Or you could use a simple CASE statement (which your code already has an example of so it is not clear why it is using DECODE() instead here).

So now we just have

case when max(CA.ATT='Test') then 'Y' else 'N' end as SAM

You cannot use OVER/PARTITION in PROC SQL.  But it is smart enough to remerge aggregate values back onto detail records, so it a lot of cases you don't need it.

So are you just trying to do something like?

proc sql;
create table ABC as 
select mdm.hpp
     , case when max(CA.ATT='Test') then 'Y' else 'N' end as SAM
from MACM as Mdm
left join CCCT as CA
on MDM.ALKP = CA.ALKP
group by mdm.hpp
;
quit;

If you add some other non grouping and non aggregate variables to the list of selected variables then SAS will remerge the values of SAM back onto the detail records based on the value of MDM.HPP.