Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

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;


Accepted Solutions
Solution
‎05-18-2015 12:31 PM
Super Contributor
Posts: 578

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

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;

View solution in original post


All Replies
Solution
‎05-18-2015 12:31 PM
Super Contributor
Posts: 578

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

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;

Occasional Contributor
Posts: 19

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

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

Super User
Super User
Posts: 6,497

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

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;

Occasional Contributor
Posts: 19

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

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

Super User
Super User
Posts: 6,497

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

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.

Occasional Contributor
Posts: 19

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

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

Super Contributor
Posts: 426

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

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;

Super User
Super User
Posts: 6,497

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

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 2046 views
  • 3 likes
  • 4 in conversation