how to create a duplicate record of by a sort order and skipping a record with missing values

[knveraraju91]

Dear, In the below pgm i need to create a duplicate record for last.param record only if that record has non-missing value for value variable. If there is missing value for that record then i have to next previous record  and create duplicate record. Thank you

for param =a , i am able create a duplicate record by taking last.param.  But param=b, i need help in my code. thank you

output needed

id param  adt    value   source

1 a 2016-01-01 10       original
1 a 2016-02-01 20     original
1 a 2016-03-01 30       original  

1 a 2016-03-01 30      copy
1 b 2016-01-01 10     original
1 b 2016-01-04 10     original
1 b 2016-02-01 20     original

1 b 2016-02-01 20     copy
1 b 2016-03-01          original

data have;
input id param$ adt yymmdd10. value 16-18;
format adt date9.;
datalines;
1 a 2016-01-01 10
1 a 2016-02-01 20
1 a 2016-03-01 30
1 b 2016-01-01 10
1 b 2016-01-04 10
1 b 2016-02-01 20
1 b 2016-03-01 
;
run;
proc sort data=have;
by id param adt;
run;
data want;
set have;
source='original';
 output;
 by id param adt;
 if last.param and value ^=. then do;
 source='copy';
 output;end;
 run;

[Astounding]

As you have probably seen by now, your approach adds to many copies. While it can be fixed, it's easier to use 2 steps instead of 1. For example, after sorting:

data copies;
set have;
by ID param adt;
Where value > .;
if last.param;
run;

data want;
set have (in=original) copies;
by ID param adt;
if original then source='original';
else source='copy';
run;

[Satish_Parida]

Solution:

data have;
input id param$ adt yymmdd10. value 16-18;
format adt date9.;
datalines;
1 a 2016-01-01 10
1 a 2016-02-01 20
1 a 2016-03-01 30
1 b 2016-01-01 10
1 b 2016-01-04 10
1 b 2016-02-01 20
1 b 2016-03-01 
;
run;

proc sort data=have;
by id param descending adt;
run;

data want(drop=f);
set have;
source='original';
output;
by id param descending adt;
retain f;
if first.param then f=1;
if f=1 and value ^=. then do;
	source='copy';
	output;
	f=0;
end;
run;

proc sort data=want;
by id param adt;
run;

Let us know if it worked for you.

[Jagadishkatam]

Please try the below code with do loop

data have;
input id param$ adt yymmdd10. value 16-18;
format adt date9.;
datalines;
1 a 2016-01-01 10
1 a 2016-02-01 20
1 a 2016-03-01 30
1 b 2016-01-01 10
1 b 2016-01-04 10
1 b 2016-02-01 20
1 b 2016-03-01 
;
run;


proc sort data=have;
by id param;
run;

data want;
length source $20.;
do until(last.param);
set have;
where value ne .;
by id param;
if last.param then source='copy';
ord=1;
if last.param;
output;
end;
do until(last.param);
set have;
by id param;
source='original';
ord=.;
output;
end;
run;

proc sort data=want;
by id param adt ord;
run;

[Satish_Parida]

Isn't this the solution you looked for? @knveraraju91