Help using Base SAS procedures

conditional set statement

Reply
Occasional Contributor
Posts: 13

conditional set statement

data test1;
input x z $;
datalines;
1 A
2 B
3 C
;

data test2;
input x r $;
datalines;
1 R
2 S
3 T
4 W
5 G
;

For the two datasets above, I am trying to concatenate them to produce a third which will only have x variables found on test1. For instance, the kind of thing you would easily do for a merge by using the subsetting if statement. I can't seem to find a way to do the same with set statement or anything else.

For instance, I'll like to obtain an output like this:

x z r
1 A .
1 . R
2 B .
2 . S
3 C .
3 . T

Thanks for any help. Message was edited by: Elkridge_SAS
Valued Guide
Posts: 2,175

Re: conditional set statement

SET with a BY statement and an IN= to detect the test1 and from that create and retain a flag when test1 is the first.id
Data result ;
Set test1( in= in1) test2;
By X;
If first.X then flag = in1;
Retain flag;
Drop flag;
If in1;
Run;
Occasional Contributor
Posts: 13

Re: conditional set statement

No this gives me only 3 records. I want 5. This might help though. I'll try and tweak it. If you notice any corrections that should be made, I'll be glad to receive your help.
Valued Guide
Posts: 2,175

Re: conditional set statement

Elkridge
sorry for posting untested code.A small change provides what you wanted.
I created FLAG and failed to test it, testing in1 instead
The only change
if flag;
Data result ;
Set test1( in= in1) test2;
By X;
If first.X then flag = in1;
Retain flag;
Drop flag;
If flag;
Run;
option ls=64 nocenter dtreset pageno=1 ;
title 'mea culpa';
proc print;
run;[pre]mea culpa 23:40 Friday, February 4, 2011 1

Obs x z r

1 1 A
2 1 R
3 2 B
4 2 S
5 3 C
6 3 T[/pre]
Regular Contributor
Posts: 241

Re: conditional set statement

Then one way is to merge them first, then output twice, missing out one variable at a time.


   /* test datasets */


   data one(drop=r) two(drop=z);


      do until(x = 3); 


         input x z $ @@; 


         output one; 


      end


      input;


      do until(x = 5); 


         input x r $ @@; 


         output two; 


      end


      stop;


   cards;


   1 A 2 B 3 C


   1 R 2 S 3 T 4 W 5 G


   ;


   run;


 


   /* sort, merge, then output twice missing out either z or r */


   proc sort data=one out=ones nodupkey


      by x; 


   run;


   proc sort data=two out=twos nodupkey


      by x; 


   run;


 


   data three;


      merge ones(in=one) twos(in=two);


      by x;


      if one and two;


 


      array temp(1) $1 _temporary_;


      temp(1) = r;


      call missing(r);


      output;


      r = temp(1);


      call missing(z);


      output;


   run;


 


   /* check */


   proc print data=three noobs;


   run;


   /* on log


   x    z    r


   1    A


   1         R


   2    B


   2         S


   3    C


   3         T


   */

Occasional Contributor
Posts: 13

Re: conditional set statement

This does exactly what I wanted. Thanks to all those who helped.

data result(drop=x_keep) ;
set test1(in=in1) test2;
by X;
if first.X and in1 then x_keep = X;
retain x_keep;
if X=x_keep;
run;
Super User
Posts: 10,500

Re: conditional set statement

proc sql;
create table test3 as
select * from test1
outer union corr
select b.*
from (select distinct x from test1) as a left join test2 as b on a.x=b.x

order by x;
quit;

May execute faster on larger datasets.
N/A
Posts: 0

Re: conditional set statement

Hello,

Here is another solution which also accounts (like chang's) for different values for the key variable in both data sets not just in one:



[pre]

data test1;
input x z $;
datalines;
1 A
2 B
3 C
7 G
9 U
;

data test2;
input x r $;
datalines;
1 R
2 S
3 T
4 W
5 G
8 P
9 Q
;

data test3;

retain _x a b;

set test1 (in=t1) test2 (in=t2);
by x;

if t1 then do;
_x=x;c+1;
a=c;
return;
end;

if t2 then do;
d+1;
b=d;
end;

if x ne _x then return;

set test1 point=a ;
call missing(r);
output;
set test2 point=b;
call missing(z);
output;

drop _x c d;

run;

[/pre]

Marius

Ask a Question
Discussion stats
  • 7 replies
  • 393 views
  • 0 likes
  • 5 in conversation