DATA Step, Macro, Functions and more

how to add a column just in one procedure

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

how to add a column just in one procedure

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.

 


Accepted Solutions
Solution
‎07-21-2016 06:44 PM
Regular Contributor
Posts: 237

Re: how to add a column just in one procedure

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎07-21-2016 06:44 PM
Regular Contributor
Posts: 237

Re: how to add a column just in one procedure

[ Edited ]

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;

Contributor
Posts: 31

Re: how to add a column just in one procedure

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

Super User
Super User
Posts: 7,988

Re: how to add a column just in one procedure

[ Edited ]

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;
Contributor
Posts: 27

Re: how to add a column just in one procedure

Are you looking for something like this?

 

data want;

set aa;

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

else k = "N";

run;

Contributor
Posts: 31

Re: how to add a column just in one procedure

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 383 views
  • 1 like
  • 4 in conversation