topic Re: How to do write sql code for data step code in SAS Programming

How to do write sql code for data step code

knveraraju91 — Thu, 16 Jul 2020 16:27:01 GMT

Dear,

I got output i need but the pgm looks lengthy. Please suggest some alternative way or sql code. Thank you

I need to select for day variable value and create impday for each id by selecting first obs after last obs where flag='N'.

for id=1, the first obs after last obs where flag='N' is day=5. so IMPDAY=5

for id=2, the first obs after last obs where flag='N' is day=5. so IMPDAY=5

for id=3, there is no obs where flag='N' so i need to take first obs

for id=4 there is one record so i select

output needed

id flag day impday

1 Y 1 5
1 Y 2 5
1 Y 3 5
1 N 4 5
1 Y 5 5
1 Y 6 5
2 N 1 5
2 Y 2 5
2 Y 3 5
2 N 4 5
2 Y 5 5
2 Y 6 5
3 Y 1 1
3 Y 2 1
3 Y 3 1
4 Y 1 1

data one;
input id flag$ day;
datalines;
1 Y 1
1 Y 2
1 Y 3
1 N 4
1 Y 5
1 Y 6
2 N 1
2 Y 2
2 Y 3
2 N 4
2 Y 5
2 Y 6
3 Y 1
3 Y 2
3 Y 3
4 Y 1
;

proc sort data=one;
by id descending flag;
run;

data two;
set one;
by id descending flag;
if last.id;
p_flag=flag;
p_day=day;
drop flag day;
run;

data three;
merge one(in=a) two(In=b);
by id;
if a;
if p_flag='N' and day lt p_day then delete;
run;

proc sort data=three;
by id flag;
run;

data four;
set three;
by id flag;
if flag='N' and first.id ^=last.id then delete;
run;

proc sort data=four;
by id flag;
run;
data five;
set four;
by id flag;
if first.id;
impday=day;
keep id impday;
run;

data six;
merge one(in=a) five;
by id;
if a;
run;

Re: How to do write sql code for data step code

ballardw — Thu, 16 Jul 2020 16:45:03 GMT

Since Proc SQL does not really have a good sense of "order" of records your "first obs after Last obs flag='N' " it is unlikely to be a good tool alone.

What do you want when the last observation for an ID has flag="N"? The next observation is from a different ID.

Re: How to do write sql code for data step code

knveraraju91 — Thu, 16 Jul 2020 16:53:13 GMT

Thank you very much for support. i set to missing if id has only one record and has "N'

Re: How to do write sql code for data step code

Reeza — Thu, 16 Jul 2020 17:01:02 GMT

Personally I prefer a data step but here are two approaches.

proc sort data=one; 
by id day;
run;

data temp;
set one (where=(flag='N'));
by ID;
impDay = day+1;
if last.ID;
keep id impday;
run;

data want;
merge one temp;
by id;
if missing(impday) then impday = 1;
run;

SQL option


proc sql;
create table want_sql as
select t1.*, coalesce(t2.max_day, 1) as impday
from one as t1
left join (select id, max(day)+1 as max_day from one where flag='N' group by ID) t2
on t1.id=t2.id;
quit;

Re: How to do write sql code for data step code

knveraraju91 — Thu, 16 Jul 2020 19:15:47 GMT

Thank you very much for support. The code is working for some OBS only if DAY values are sequential order. But if day variable values are not sequential order impday= DAY+1 is not giving me correct output as mentioned in code. I need to take next OBS after last obs having flag='N'. Please suggest.

Re: How to do write sql code for data step code

Reeza — Thu, 16 Jul 2020 19:38:25 GMT

Please provide sample data that reflects your business requirements.

Re: How to do write sql code for data step code

ballardw — Thu, 16 Jul 2020 20:10:31 GMT

@knveraraju91 wrote:

Thank you very much for support. The code is working for some OBS only if DAY values are sequential order. But if day variable values are not sequential order impday= DAY+1 is not giving me correct output as mentioned in code. I need to take next OBS after last obs having flag='N'. Please suggest.

Since all of your example data shows DAY as sequential you implied that was the case.

If you do not provide representative data it is hard to generate a general solution.

And per my previous question what if the last value when you have more than one value in an id group that the flag is 'N'. You only answered for a specific case of exactly one value.