topic How to retrieve a single row from the output in SAS Programming

How to retrieve a single row from the output

Sheeba — Fri, 03 Feb 2017 16:28:48 GMT

have a question related to the following code.

Temp1, Temp2, Temp3, Temp4 are variables from dataset temp.

Ds1, ds2, ds3, ds4 are variables from the dataset2.

Code is the variable from temp and code has been renamed to _code1 in dataset2.

For a particular records in temp the value of mid=1 and pid =6. It has a single match in dataset 2.

data Temp;

input Code $ mid pid temp1 $ temp2 $ temp3 $ temp4 $;

datalines;

AB 1 6 aaa bbb xxx yyy

;

run;

data Dataset2;

input Code $ ds1 $ ds2 $ ds3 $ ds4 $;

datalines;

AB aaa bbb xxx yyy

;

run;

data two;

set temp;

* Drop temporary variables;

DO i=1 TO xnobs;

SET dataset2 (rename=(code=_code1)) NOBS=xnobs POINT=i;

if code=_code1 then

do;

if mid=1 then

do;

if temp1=ds1 and temp2 = ds2 then

do;

m_match='Y';

output;

end;

if pid =6 then

if temp3 = ds3 and temp4=ds4 then

do;

p_match='Y';

output;

end;

run;

The problem is when I run the below query the output has 2 rows where as I need one row in the output which has both m_match=’Y’ and p_match =’Y’.

Also if by any chance if the output has only one row with either p_match='Y' or m_match='Y', i need to retrieve that as well

It will be great if you can help me with this.

Editing the message to add few more details

Please find the output below

Code

mid

pid

temp1

temp2

temp3

temp4

uni_seq_num

_code1

ds1

ds2

ds3

ds4

m_match

p_match

aaa

bbb

xxx

yyy

36111

aaa

bbb

xxx

yyy

aaa

bbb

xxx

yyy

36111

aaa

bbb

xxx

yyy

One thing which i noticed is even though the conditions are matching (temp3=ds3 and temp4=ds4) p_match= set to blank. I am lil confused on this.

Thanks in advance,

Regards,

Sheeba Swaminathan

Re: How to retrieve a single row from the output

RW9 — Fri, 03 Feb 2017 13:56:43 GMT

I am really not sure here what you ar trying to do. From what I can work out of your post, then this:

data two;
  merge temp dataset2;
  by code;
  m_match=ifc(temp1=ds1 and temp2=ds2,"Y","");
  p_match=ifc(temp3=ds3 and temp4=ds4,"Y","");
run;

Should be all you need. If there is multiple rows, then retain m_match and p_match and if last.code then output? Maybe clearly explain what you mean, update test data to reflect the scenarios, and provide what the output from that data should be. It maybe you can simply use exists() function in SQL.

Re: How to retrieve a single row from the output

Tom — Fri, 03 Feb 2017 14:01:51 GMT

Only execute the OUTPUT statement once if you only want to write one record. So your program flow should be.

Calculate FLAG1 ;
Calculate FLAG2 ;
IF FLAG1 and FLAG2 then output;

In general I find it much easier to code if I use boolean values instead of characters strings. For this type of problem initialize the flags to false and then set true when your condition is matched.

m_match=0;
p_match=0;
if code=_code1 then do;
  if mid=1 and temp1=ds1 and temp2=ds2 then m_match=1;
  if pid =6 and temp3=ds3 and temp4=ds4 then p_match=1;
end;
if m_match and p_match then output;

Also it really seems your program is way too complicated. If you want to merge on the value of CODE then just use the MERGE statement.

data want ;
  merge temp dataset2 ;
  by code ;
  if  (mid=1 and temp1=ds1 and temp2=ds2)
  and (pid=6 and temp3=ds3 and temp4=ds4)
  ;
run;

Re: How to retrieve a single row from the output

Astounding — Fri, 03 Feb 2017 14:13:06 GMT

And if no rows match on both, but you have one row with m_match='Y' and a different row with p_match='Y', do you want to output both of them?

Any of this can be done with a program that builds on the RW9 solution:

data want;

m_match=' ';

p_match=' ';

do until (last.code);

merge temp dataset2;

by code;

m_match=;

p_match=;

* Track the combinations found;

end;

* Check the combinations found and decide which combinations should be output;

do until (last.code);

merge temp dataset2;

by code;

m_match=

p_match=

if /* right combination is found */ then output;

end;

run;

Re: How to retrieve a single row from the output

Sheeba — Fri, 03 Feb 2017 15:05:49 GMT

Hi RW9,

Thanks a lot for the solution.

The problem is that the join is many to many and hence match merge is not giving exact results. I will try to add the data to reflect actual scenarios.

Regards,

Sheeba

Re: How to retrieve a single row from the output

Sheeba — Fri, 03 Feb 2017 15:08:20 GMT

Hi Tom,

thanks a lot for the reply.

I will modify the flags.

The problem here is join is many to many and match merge doesnt' have expected results.

Regards,

Sheeba