topic Re: how to add a column just in one procedure in SAS Programming

how to add a column just in one procedure

cecily — Thu, 21 Jul 2016 20:43:43 GMT

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.

Re: how to add a column just in one procedure

gamotte — Thu, 21 Jul 2016 21:04:01 GMT

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;

Re: how to add a column just in one procedure

PBsas — Thu, 21 Jul 2016 21:39:52 GMT

Are you looking for something like this?

data want;

set aa;

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

else k = "N";

run;

Re: how to add a column just in one procedure

cecily — Thu, 21 Jul 2016 22:42:59 GMT

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

Re: how to add a column just in one procedure

cecily — Thu, 21 Jul 2016 22:44:30 GMT

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!

Re: how to add a column just in one procedure

RW9 — Fri, 22 Jul 2016 09:40:36 GMT

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;