how to add a column just in one procedure

[cecily]

Data aa;
input X$ Z $;
datalines;
eliy N
eliya Y
ais N
shsi N
;
run;

I want to add a column ‘K’. If column'Z' contains Y, then all values in column 'K' are Y, else are N.

Who could tell me ....if I could do this in just one data step or one proc sql?I don't hope to first get a dataset then merge or join.

[gamotte · Accepted solution]

Hello,

Not tested :
Proc sql;
CREATE TABLE want AS
SELECT a.*, b.K
FROM aa AS a
LEFT JOIN (
SELECT CASE WHEN SUM(Z="Y") THEN "Y" ELSE "N"
END AS K
FROM aa
) AS b
ON 1;
quit;

[cecily]

oh!!This is what I want! Thank you very much!

[RW9]

Or to simplfy:

proc sql;
  create table WANT as
  select  *,
          case when exists(select distinct Z from AA where Z="Y") then "Y"
               else "N" end as K
  from    AA;
quit;

Actually, saying that:

proc sql;
  create table WANT as
  select  *,
          coalesce((select distinct Z from AA where Z="Y"),"N") as K
  from    AA;
quit;

[PBsas]

Are you looking for something like this?

data want;

set aa;

if z = "Y" then K = "Y";

else k = "N";

run;

[cecily]

No. what I want is all values for that new column are ‘Y’ if column 'Z' contains 'Y'.  I have got the right answer. Thank you very much!