topic Re: How to create a new column based on values of an other column? in SAS Enterprise Guide

How to create a new column based on values of an other column?

AK100 — Tue, 15 Sep 2020 11:27:48 GMT

I have a dataset called `my_dataset`. Now I want to create a new column (marked) that puts a * sign for every value in column color if that value is blue. What is the best way to do it?

This is my reproducible dataset.

data my_dataset;
input customer $ ref color $20.;
datalines;
C1 101 red
C2 102 blue
C3 103 blue
C4 104 red
C5 105 yellow 
C6 106 blue 
;
run;

This is my expected outcome:

customer -- ref -- color -- marked
C1 -- 101 -- red --
C2 -- 102 -- blue -- *
C3 -- 103 -- blue -- *
C4 -- 104 -- red --
C5 -- 105 -- yellow --
C6 -- 106 -- blue -- *

Re: How to create a new column based on values of an other column?

Tom — Tue, 15 Sep 2020 11:50:34 GMT

A simple IF statement will let your conditionally assign values to your new variable.

data want;
  set my_dataset;
  if color='blue' then marked='*';
run;

Re: How to create a new column based on values of an other column?

AK100 — Tue, 15 Sep 2020 12:11:29 GMT

Thank you Tom. Short addition. What if Im looking for If contains 'blue'? for example there might be some values 'blueblack' or 'whiteblue'. I just need everything that contains blue to be marked with an ' * '. Hows that done?

Re: How to create a new column based on values of an other column?

Tom — Tue, 15 Sep 2020 12:21:26 GMT

Just change the condition you are using in the IF statement. For example you could use the INDEX() function

if index(color,'blue') then .....

or the FIND() function or even one of the PRX.... functions that use regular expressions. You might want to make the test case insensitive.

if index(lowcase(color),'blue') then .....

Re: How to create a new column based on values of an other column?

AK100 — Tue, 15 Sep 2020 13:25:33 GMT

Thank you possible to add an else statement after then?

Re: How to create a new column based on values of an other column?

Tom — Tue, 15 Sep 2020 14:00:43 GMT

Sure. Check the documentation. https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1j60arf27ll4nn1ejavv3nby4pa.htm&docsetVersion=9.4&locale=en

Re: How to create a new column based on values of an other column?

ballardw — Tue, 15 Sep 2020 14:27:01 GMT

What do those dashes mean in the "expected output"?