CODEWITHSUNDEEP
sasproc-sql
user11505060
user11505060

Reputation:

SAS Same column selection multiple times using multiple where clause as different output columns

I have a temp table that is being created, we will say that column 1 is YearMonth, column2 as user_id, Column 3 is flag1, column 4 is flag.

YearMonth User_id   Flag1 Flag2
200101    1         N     N
200101    2         N     N
200101    3         Y     N

I want to minimum YearMonth based on the flags.

So for e.g. when the flag1=N then grab the least YearMonth for each userid, and same way for the flag2=N then grab least YearMonth for each userid. Note: Here, if Flag1 is N then Flag2 will always be N but it is possible that Flag2 has more userids than Flag1.

I am new to SAS and when I try to use case statement like below:

   SELECT distinct t1.userid,
      case when t1.flag1='N' then MIN(t1.YearMonth) end as YearMonth1,
      case when t1.flag2='N' then MIN(t1.YearMonth) end as YearMonth2
   FROM WORK.table t1
   GROUP BY 1;

I would like to know why it will not produce the output as below:

Userid YearMonth1 Yearmonth2
1      202001     202001
2      202001     202001
3                 202001

What am I missing here? Thanks

Upvotes: 1

Views: 305

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you just want conditional aggregation. The CASE expression is the argument to the MIN():

SELECT t1.userid,
       MIN(CASE WHEN t1.flag1 = 'N' THEN t1.YearMonth END) end as YearMonth1,
       MIN(CASE WHEN t1.flag2 = 'N' THEN t1.YearMonth END) end as YearMonth2
FROM t1
GROUP BY t1.userid

Upvotes: 1

Related Questions