How to check if an value existing in a column? and if not then add a observation to the dataset

[yangtaotai]

Hello!

I have a data set called Test and I want to check if the Site column has value of C1 or not. if not then I want to add an observation with Site = 'C1' and Value= 123. Not sure How to do it? Hope someone could give me some hint on this.

Thanks,

Tao

Data Test;

input Site $ value 10.;

cards;

A1 100

A2 102

B1 102

B2 90

C2 33

;

run;

[DBailey · Accepted solution]

I've seen two methods: 

1) create a separate table to house the new records and then append

proc sql;

create table work.recs_to_add as

select distinct

    'C1' as site,

    123 as value

from

    test t1

where

    not exists (select * from work.test where site='C1');

insert into test(site,value)

select site,value from work.recs_to_add;

quit;

or 2) create a temp variable as part of a data step:

data test;

set test end=eof;

retain tmpSite;

format tmpsite $1.;

if _N_=1 then tmpSite='N';

if site='C2' then tmpsite='Y';

output;

if eof and tmpsite='N' then do;

    site='C1';

    value=123;

    output;

    end;

run;

[yangtaotai]

Hi DBailey,

Thank you for your help and the first solution will work for my case. For the second method, because C2 may not even exist (I just randomly created the data set)

Tao

[Tom]

Why not just do a MERGE of all of the values you want to force in?

data required ;

  if 0 then set test ;

  do site='A1','C1' ;

     do value=123;

        output;

     end;

end;

run;

data want ;

  merge required test ;

run;

[yangtaotai]

Hi Tom,

Thank you for the reply and the reason why merge may not be the best practice for my case is I don't know if the original Test data set has 'C1' or not. If original Test data set has 'C1' then I don't want to add a duplication observation.

Tao

[Tom]

That is the nature of a MERGE.  The records that are in both are combined instead of the concatenation that would occur if you combined them with the SET statement.

[yangtaotai]

Hi Tom,

I see what you mean. Totally forgot the merge and always remember there is an option you could keep the duplicated obs.

Thank you for the help!

Tao

[Babloo]

What is 'if 0' means from your program?

data required ;

if 0 then set test ;

  do site='A1','C1' ;

     do value=123;

        output;

     end;

end;

run;

data want ;

  merge required test ;

run;

[Tom]

It is a method to insure that the variables in the new data step match the variable definitions in the old dataset.

The IF statement will treat 0 as false so it will never actually execute the SET statement, but the compiler will look at it and define all of the variables in that dataset.

This will prevent the variable SITE from being defined as length $2.