-3

My dataset looks like this:

AsAtDate  ReleaseDate ID Var1 Var2 ... Var5
200701     200601      1   x    .    
200702     200601      1   .    a
200703     200601      1   .    .
200701     200702      2   .    b 
200702     200702      2   y    b
200703     200702      2   y    .
200702     200501      3   z    .
200703     200501      3   .    .

I want my results to look like this:

AsAtDate  ReleaseDate ID Var1 Var2 ... Var5
200701     200601      1   x    a    
200702     200601      1   x    a
200703     200601      1   x    a
200701     200702      2   .    . 
200702     200702      2   y    b
200703     200702      2   y    b
200702     200501      3   z    .
200703     200501      3   z    .

How do I get my data to consider the ReleaseDate as in, if ReleaseDate is earlier than AsAtDate then populate the fields by ID.

user667489
  • 9,501
  • 2
  • 24
  • 35
EmShing
  • 29
  • 9
  • Please post whatever you're tried. You're likely looking at a sort and retain here. Is it possible to have different values? – Reeza Sep 21 '18 at 00:45
  • @Reeza I tried sort and retain but I can't seem to get it work – EmShing Sep 21 '18 at 01:48

2 Answers2

1

Building on your solution, we can add a CASE statement to conditionally fill in the data as needed. 

proc sql;
create table want as
select *, 
       case when ReleaseDate>AsAtDate then " " 
       else max(Var1) 
       end as _Var1
from have
group by ID
order by ID, AsAtdate;
quit;
Reeza
  • 20,510
  • 4
  • 21
  • 38
0

I tried the following code, is there a more simplified code than this? 

proc sql;
create table want as
select *, max(Var1) as _Var1
from have
group by ID
order by ID, AsAtdate;
quit;

data want1;
set want;
by id;
if ReleaseDate > AsAtDate then _Var1='';
run;
EmShing
  • 29
  • 9
  • you could probably add the last step into the PROC SQL query using a CASE statement, but other than that it seems pretty compact. – Reeza Sep 21 '18 at 15:08