BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yangtaotai
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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

8 REPLIES 8
DBailey
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 21904 views
  • 4 likes
  • 4 in conversation