topic Re: Help in my Proc SQL code in SAS Programming

Help in my Proc SQL code

knveraraju91 — Wed, 19 Aug 2020 14:44:41 GMT

Dear,

I need to create a variable in data set 'ONE' by comparing DATE variable from dataset 'ONE' and DATE and VALUE variables from data set 'TWO' .

If there is a record in dataset TWO with VALUE='N' and DATE greater than DATE in dataset ONE, then i need to create FLAG='Y'. If no such record then FLAG is "N"

Only for id=2, there is a record in data set TWO with DATE greater than date in ONE dataset and with value ="N'. Hence only ID=2 populated with flag='Y'. Please suggest in my code. I am getting all possible records. Thank you

output need;

id date flag

1 2019-01-18 N
2 2019-02-12 Y
3 2019-03-15 N

data one;
input id date yymmdd10.;
format date yymmdd10.;
datalines;
1 2019-01-18
2 2019-02-12
3 2019-03-15
;
data two;
input id date yymmdd10. value $;
format date yymmdd10.;
datalines;
1 2019-01-01 N
1 2019-01-11 Y
1 2019-01-18 N
2 2019-02-02 N
2 2019-02-22 N
3 2019-03-05 Y
3 2019-03-25 Y
;
proc sql;
create table three as
select *,
case
when a.date lt b.date and b.value='N' then 'Y'
else 'N' end as flag
from one as a left join two as b
on a.id=b.id
order by a.id,a.date;
quit;

Re: Help in my Proc SQL code

mkeintz — Wed, 19 Aug 2020 15:02:43 GMT

Are you trying to produce one record per ID with flag representing the presence of one qualifying obs from dataset two? Or do you want the traditional "one left join two" results, with one record per qualifying join?

Re: Help in my Proc SQL code

knveraraju91 — Wed, 19 Aug 2020 15:16:20 GMT

Thank you very much. I need to output one record per output with flag representing the presence of one qualifying obs from dataset two
I need to keep only records from dataset one and create a variable FLAG with Y or N depends on data set TWO has records with VALUE=N and date greater than date in date in ONE data set

Re: Help in my Proc SQL code

mkeintz — Wed, 19 Aug 2020 16:07:08 GMT

First, thank you for providing sample data in the form of a data step. It's much easier to test proposed solutions.

Here's mine: (edited to require date from two to be greated, not just greater or equal to date from one):

data one;
input id date yymmdd10.;
format date yymmdd10.;
datalines;
1 2019-01-18
2 2019-02-12
3 2019-03-15
;
data two;
input id date yymmdd10. value $;
format date yymmdd10.;
datalines;
1 2019-01-01 N
1 2019-01-11 Y
1 2019-01-18 N
2 2019-02-02 N
2 2019-02-22 N
3 2019-03-05 Y
3 2019-03-25 Y
;
data three (keep=id flag);
  set one two (where=(value='N') in=intwo);
  by id date;
  if last.id;
  if intwo=1 and first.date=1 then flag='Y';  /*Added "first.date=1" to properly satisfy date requirement*/
  else flag='N';
run;

Give the 2 datasets are sorted by ID/DATE, it's quite easy to produce what you want in a DATA step:

The SET followed by BY statements tell SAS to interleave the obs from one and two, sorted by ID/DATE.
The "where=" tells sas to process only obs from two that qualify for the value you specified.
The intwo variable is a temporary dummy saying whether the (interleaved) record-in-hand is from two.
The subsetting IF says to keep only the last record for each id.
So if the last record is from TWO (and it is not a tied date wiht a record from ONE) , it therefore has a date that qualifies for your criterion. And if not, then it doesn't, so calculate flag accordingly.

Re: Help in my Proc SQL code

mkeintz — Wed, 19 Aug 2020 16:11:11 GMT

An sql solution

proc sql;
  create table threeb as
  select a.id, 
    case when b.date>a.date then 'Y'
	     else 'N'
	end as flag

	from one as a
	left join two (where=(value='N')) as b

	on a.id=b.id
	group by a.id
	having b.date=max(b.date) ;
quit;

Re: Help in my Proc SQL code

knveraraju91 — Wed, 19 Aug 2020 17:03:06 GMT

Thank you very much for sql code. It gives me the output i need after adding a.date in select statement. As i need to keep DATE variable from dataset ONE.

But in the data step code, i got the correct FLAG variable values but I am not able to keep same DATE variable values from dataset ONE. After keeping DATE variable in Keep statement, I am getting DATE values from dataset TWO for id=2.

code worked

proc sql; create table threeb as select a.id, a.date case when b.date>a.date then 'Y' else 'N' end as flag from one as a left join two (where=(value='N')) as b on a.id=b.id group by a.id having b.date=max(b.date) ; quit;

Re: Help in my Proc SQL code

PGStats — Wed, 19 Aug 2020 17:05:12 GMT

Translate your requirement directly as a correlated subquery:

proc sql;
create table three as
select 
    id,
    date,
    case when exists (
        select * from two where id=a.id and value="N" and date > a.date) 
        then "Y" else "N" end as flag
from one as a;
quit;