0

I have a following table

Id   Values    
 1     A          
 1     A          
 1     B          
 2     @          
 2     @
 2     @
 3     A
 3     A
 3     A
 3     A
 3     @
 4     B
 4     B
 4     B

Output:

Id   Values
 1     @
 2     @
 3     A
 4     B

Within each Id group, if all values are @, I want to set the value for that Id to @ else if all values for Id are same (ignoring @s) (e.g. all As), set the value for that Id to be that value (A) else set value of id to @.

This question has been answered in sqlserver and I am trying to replicate code in SAS I need to do this in SAS. But somehow NULLIF in SAS is not working. can some one guide me how can I do this in SAS?

Richard
  • 25,390
  • 3
  • 25
  • 38
user10611398
  • 21
  • 4
  • What code did you try ? What messages were shown in the SAS log ? What was the SQL Server query with `NULLIF`? Did it involve LINQ ? – Richard Feb 25 '19 at 23:23
  • You will need to explain what `NULLIF` is (and how you are using it) if you want help from SAS programmers. – Tom Feb 26 '19 at 01:24

2 Answers2

0

One way is to count the number of distinct values in the group and assign the resultant to max(value) when there is only one value and @ otherwise.

 proc sql;
   create table want as
   select id,
     case 
       when count(
          distinct 
          case 
            when value ne '@' then value
          end
          ) = 1 then max(value)
       else '@'
     end as value
   from have
   group by id
  ;
Richard
  • 25,390
  • 3
  • 25
  • 38
0

I found NULLIF is included in Fedsql Proc and when I ran the following code it worked.

proc fedsql;
select  id,
case    when    min(NULLIF(values, '@')) = max(NULLIF(values, '@'))
        and     min(NULLIF(values, '@')) ^= '@'
        then    min(NULLIF(values, '@'))
        else    '@'
        end as result
        from    mytable
        group by id;
run;
Andreas
  • 2,455
  • 10
  • 21
  • 24
user10611398
  • 21
  • 4