topic Re: how to create a duplicate record of by a sort order and skipping a record with missing values in SAS Programming

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

knveraraju91 — Wed, 20 Feb 2019 06:52:52 GMT

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;

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

Astounding — Wed, 20 Feb 2019 09:35:29 GMT

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;

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

Satish_Parida — Wed, 20 Feb 2019 09:41:37 GMT

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.

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

Jagadishkatam — Wed, 20 Feb 2019 09:44:30 GMT

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;

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

Satish_Parida — Wed, 20 Feb 2019 18:30:07 GMT

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