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

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

yangtaotai — Mon, 18 May 2015 16:19:26 GMT

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;

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

DBailey — Mon, 18 May 2015 16:31:22 GMT

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;

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

yangtaotai — Mon, 18 May 2015 17:25:47 GMT

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

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

Tom — Mon, 18 May 2015 17:48:08 GMT

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;

run;

data want ;

merge required test ;

run;

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

yangtaotai — Mon, 18 May 2015 19:39:38 GMT

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

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

Tom — Mon, 18 May 2015 21:08:58 GMT

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.

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

yangtaotai — Mon, 18 May 2015 21:14:20 GMT

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

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

Babloo — Tue, 19 May 2015 07:52:22 GMT

What is 'if 0' means from your program?

data required ;

if 0 then set test ;

do site='A1','C1' ;

do value=123;

output;

end;

run;

data want ;

merge required test ;

run;

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

Tom — Tue, 19 May 2015 11:27:52 GMT

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.