topic Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost) in SAS Procedures

By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

Nikos — Sun, 28 Sep 2014 15:32:12 GMT

Dear all,

In a by group (id1,id2, TRADE_QTR - grouping vars) I have missing values in variable STATUS for various Trade_Quarters.

I would like to replace the STATUS missing values based on the following:

-- If STATUS = 'C' (=cancelled) then if any SUBSEQUENT TRADE_QUARTERS take STATUS ='C'

-- If STATUS = 'C' (=cancelled) then if any PRIOR TRADE_QUARTERS take STATUS = 'V' (=Valid)

-- If there is no STATUS = 'C' then ALL values of STATUS become = 'V'

Pls. see data HAVE and WANT in the attached file.

Thanking you in advance for any hint.

Kind regards

Nikos

DATA HAVE						DATA WANT
STATUS	ID1	ID2	TRADE_QTR	STATUS_QTR	PRICE	STATUS	ID1	ID2	TRADE_QTR	STATUS_QTR	PRICE
	FG	5276	2009Q1	.	0	V	FG	5276	2009Q1	.	0
	FG	5276	2009Q2	.	0	V	FG	5276	2009Q2	.	0
V	FG	5276	2009Q3	2009Q3	0	V	FG	5276	2009Q3	2009Q3	0
C	FG	5276	2009Q4	2009Q4	18508	C	FG	5276	2009Q4	2009Q4	18508
	FG	5276	2010Q1	.	0	C	FG	5276	2010Q1	.	0
	FG	5276	2010Q2	.	0	C	FG	5276	2010Q2	.	0
	FG	5276	2010Q3	.	0	C	FG	5276	2010Q3	.	0
	FG	5276	2010Q4	.	0	C	FG	5276	2010Q4	.	0
	FG	5276	2011Q1	.	0	C	FG	5276	2011Q1	.	0
	FG	5276	2011Q2	.	0	C	FG	5276	2011Q2	.	0
	FG	5276	2011Q3	.	0	C	FG	5276	2011Q3	.	0
	FG	5276	2011Q4	.	0	C	FG	5276	2011Q4	.	0
	FG	5276	2012Q1	.	0	C	FG	5276	2012Q1	.	0
	FG	5276	2012Q2	.	0	C	FG	5276	2012Q2	.	0
	FG	5276	2012Q3	.	0	C	FG	5276	2012Q3	.	0
	FG	5276	2012Q4	.	0	C	FG	5276	2012Q4	.	0
	FG	5276	2013Q1	.	0	C	FG	5276	2013Q1	.	0
	FG	5276	2013Q2	.	0	C	FG	5276	2013Q2	.	0
	FG	5276	2013Q3	.	0	C	FG	5276	2013Q3	.	0
	FG	5276	2013Q4	.	0	C	FG	5276	2013Q4	.	0
	FG	5276	2014Q1	.	0	C	FG	5276	2014Q1	.	0
	FG	5276	2014Q2	.	0	C	FG	5276	2014Q2	.	0
	KL	4636186	2009Q1	.	0	V	KL	463	2009Q1	.	0
	KL	4636186	2009Q2	.	0	V	KL	463	2009Q2	.	0
V	KL	4636186	2009Q3	2009Q3	0	V	KL	463	2009Q3	2009Q3	0
V	KL	4636186	2009Q4	2009Q4	0	V	KL	463	2009Q4	2009Q4	0
V	KL	4636186	2010Q1	2010Q1	0	V	KL	463	2010Q1	2010Q1	0
V	KL	4636186	2010Q2	2010Q2	0	V	KL	463	2010Q2	2010Q2	0
V	KL	4636186	2010Q3	2010Q3	0	V	KL	463	2010Q3	2010Q3	0
V	KL	4636186	2010Q4	2010Q4	0	V	KL	463	2010Q4	2010Q4	0
V	KL	4636186	2011Q1	2011Q1	0	V	KL	463	2011Q1	2011Q1	0
C	KL	4636186	2011Q2	2011Q2	0	C	KL	463	2011Q2	2011Q2	0
	KL	4636186	2011Q3	.	0	C	KL	463	2011Q3	.	0
	KL	4636186	2011Q4	.	0	C	KL	463	2011Q4	.	0
	KL	4636186	2012Q1	.	0	C	KL	463	2012Q1	.	0
	KL	4636186	2012Q2	.	0	C	KL	463	2012Q2	.	0
	KL	4636186	2012Q3	.	0	C	KL	463	2012Q3	.	0
	KL	4636186	2012Q4	.	0	C	KL	463	2012Q4	.	0
	KL	4636186	2013Q1	.	0	C	KL	463	2013Q1	.	0
	KL	4636186	2013Q2	.	0	C	KL	463	2013Q2	.	0
	KL	4636186	2013Q3	.	0	C	KL	463	2013Q3	.	0
	KL	4636186	2013Q4	.	0	C	KL	463	2013Q4	.	0
	KL	4636186	2014Q1	.	0	C	KL	463	2014Q1	.	0
	KL	4636186	2014Q2	.	0	C	KL	463	2014Q2	.	0
	JK	4636650	2009Q1	.	0	V	JK	650	2009Q1	.	0
	JK	4636650	2009Q2	.	0	V	JK	650	2009Q2	.	0
C	JK	4636650	2009Q3	2009Q3	1637	C	JK	650	2009Q3	2009Q3	1637
	JK	4636650	2009Q4	.	0	C	JK	650	2009Q4	.	0
	JK	4636650	2010Q1	.	0	C	JK	650	2010Q1	.	0
	JK	4636650	2010Q2	.	0	C	JK	650	2010Q2	.	0
	JK	4636650	2010Q3	.	0	C	JK	650	2010Q3	.	0
	JK	4636650	2010Q4	.	0	C	JK	650	2010Q4	.	0
	JK	4636650	2011Q1	.	0	C	JK	650	2011Q1	.	0
	JK	4636650	2011Q2	.	0	C	JK	650	2011Q2	.	0
	JK	4636650	2011Q3	.	0	C	JK	650	2011Q3	.	0
	JK	4636650	2011Q4	.	0	C	JK	650	2011Q4	.	0
	JK	4636650	2012Q1	.	0	C	JK	650	2012Q1	.	0
	JK	4636650	2012Q2	.	0	C	JK	650	2012Q2	.	0
	JK	4636650	2012Q3	.	0	C	JK	650	2012Q3	.	0
	JK	4636650	2012Q4	.	0	C	JK	650	2012Q4	.	0
	JK	4636650	2013Q1	.	0	C	JK	650	2013Q1	.	0
	JK	4636650	2013Q2	.	0	C	JK	650	2013Q2	.	0
	JK	4636650	2013Q3	.	0	C	JK	650	2013Q3	.	0
	JK	4636650	2013Q4	.	0	C	JK	650	2013Q4	.	0
	JK	4636650	2014Q1	.	0	C	JK	650	2014Q1	.	0
	JK	4636650	2014Q2	.	0	C	JK	650	2014Q2	.	0
V	MN	378794	2009Q1	2009Q1	24394	V	MN	794	2009Q1	2009Q1	24394
	MN	378794	2009Q2	.	0	V	MN	794	2009Q2	.	0
	MN	378794	2009Q3	.	0	V	MN	794	2009Q3	.	0
	MN	378794	2009Q4	.	0	V	MN	794	2009Q4	.	0
	MN	378794	2010Q1	.	0	V	MN	794	2010Q1	.	0
C	MN	378794	2010Q2	2010Q2	-6296	C	MN	794	2010Q2	2010Q2	-6296
	MN	378794	2010Q3	.	0	C	MN	794	2010Q3	.	0
	MN	378794	2010Q4	.	0	C	MN	794	2010Q4	.	0
	MN	378794	2011Q1	.	0	C	MN	794	2011Q1	.	0
	MN	378794	2011Q2	.	0	C	MN	794	2011Q2	.	0
	MN	378794	2011Q3	.	0	C	MN	794	2011Q3	.	0
	MN	378794	2011Q4	.	0	C	MN	794	2011Q4	.	0
	MN	378794	2012Q1	.	0	C	MN	794	2012Q1	.	0
	MN	378794	2012Q2	.	0	C	MN	794	2012Q2	.	0
	MN	378794	2012Q3	.	0	C	MN	794	2012Q3	.	0
	MN	378794	2012Q4	.	0	C	MN	794	2012Q4	.	0
	MN	378794	2013Q1	.	0	C	MN	794	2013Q1	.	0
	MN	378794	2013Q2	.	0	C	MN	794	2013Q2	.	0
	MN	378794	2013Q3	.	0	C	MN	794	2013Q3	.	0
	MN	378794	2013Q4	.	0	C	MN	794	2013Q4	.	0
	MN	378794	2014Q1	.	0	C	MN	794	2014Q1	.	0
	MN	378794	2014Q2	.	0	C	MN	794	2014Q2	.	0
V	BV	378794	2009Q1	2009Q1	0	V	BV	5879	2009Q1	2009Q1	0
V	BV	378794	2009Q2	2009Q2	4469	V	BV	5879	2009Q2	2009Q2	4469
V	BV	378794	2009Q3	2009Q3	6800	V	BV	5879	2009Q3	2009Q3	6800
	BV	378794	2009Q4	.	0	V	BV	5879	2009Q4	.	0
	BV	378794	2010Q1	.	0	V	BV	5879	2010Q1	.	0
	BV	378794	2010Q2	.	0	V	BV	5879	2010Q2	.	0
	BV	378794	2010Q3	.	0	V	BV	5879	2010Q3	.	0
	BV	378794	2010Q4	.	0	V	BV	5879	2010Q4	.	0
	BV	378794	2011Q1	.	0	V	BV	5879	2011Q1	.	0
	BV	378794	2011Q2	.	0	V	BV	5879	2011Q2	.	0
	BV	378794	2011Q3	.	0	V	BV	5879	2011Q3	.	0
	BV	378794	2011Q4	.	0	V	BV	5879	2011Q4	.	0
	BV	378794	2012Q1	.	0	V	BV	5879	2012Q1	.	0
	BV	378794	2012Q2	.	0	V	BV	5879	2012Q2	.	0
	BV	378794	2012Q3	.	0	V	BV	5879	2012Q3	.	0
	BV	378794	2012Q4	.	0	V	BV	5879	2012Q4	.	0
	BV	378794	2013Q1	.	0	V	BV	5879	2013Q1	.	0
	BV	378794	2013Q2	.	0	V	BV	5879	2013Q2	.	0
	BV	378794	2013Q3	.	0	V	BV	5879	2013Q3	.	0
	BV	378794	2013Q4	.	0	V	BV	5879	2013Q4	.	0
	BV	378794	2014Q1	.	0	V	BV	5879	2014Q1	.	0
C	BV	378794	2014Q2	2014Q2	-4874	C	BV	5879	2014Q2	2014Q2	-4874
	BV	5879	2010Q4	.	0	O	BV	5879	2010Q4	.	0
	BV	5879	2011Q1	.	0	O	BV	5879	2011Q1	.	0
	BV	5879	2011Q2	.	0	O	BV	5879	2011Q2	.	0
V	BV	5879	2011Q3	2011Q3	980	O	BV	5879	2011Q3	2011Q3	980
	BV	5879	2011Q4	.	0	O	BV	5879	2011Q4	.	0
	BV	5879	2012Q1	.	0	O	BV	5879	2012Q1	.	0
	BV	5879	2012Q2	.	0	O	BV	5879	2012Q2	.	0
	BV	5879	2012Q3	.	0	O	BV	5879	2012Q3	.	0
	BV	5879	2012Q4	.	0	O	BV	5879	2012Q4	.	0
	BV	5879	2013Q1	.	540	O	BV	5879	2013Q1	.	540
	BV	5879	2013Q2	.	0	O	BV	5879	2013Q2	.	0
	BV	5879	2013Q3	.	0	O	BV	5879	2013Q3	.	0
	BV	5879	2013Q4	.	0	O	BV	5879	2013Q4	.	0
	BV	5879	2014Q1	.	0	O	BV	5879	2014Q1	.	0
	BV	5879	2014Q2	.	0	O	BV	5879	2014Q2	.	0

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

gergely_batho — Sun, 28 Sep 2014 17:31:30 GMT

Hi Nikos,

I don't give you a detailed solution (because I'm not sure I understood correctly the rules), but I think you can use the technique below to solve this problem.

In your previous post you didn't indicate what is wrong with the provided solutions. You can freely make comments on anyone's solution.

The technique is called double DOW loop, and you can use it in cases, where first you need to pass through one group, calculate something, than use that calculated something in a second pass.

In this case I'm just using it, because I need to look ahead until the next non-missing STATUS or until the end of the group.

I think TRADE_QTR is not a grouping variable, but your dataset is (and should be) grouped by id1 and id2 and sorted by TRADE_QTR.

I am using id1, id2 and STATUS in the by statement, but this is only a trick: I can easily identify consecutive C, consecutive V or consecutive missing STATUSes with it.

Code not tested yet. Tested.

data want;

do until(last.id2 or (last.STATUS and STATUS ne ''));

set have;

by id1 id2 STATUS notsorted;

if first.id2 then do;

STATUS_prev='';

end;

/*probably this should be changed, if your logic is different than I think:*/

if STATUS ~= '' and STATUS_prev = '' then STATUS_group='V';

else if STATUS_prev ~='' then STATUS_group=STATUS_prev;

else STATUS_group='V';

do until(last.id2 or (last.STATUS and STATUS ne ''));

set have;

by id1 id2 STATUS notsorted;

if STATUS='' then do;

STATUS=STATUS_group;

end;

output;

end;

retain STATUS_prev;

STATUS_prev=STATUS;

run;

An SQL based solution could also work, but it runs slowly on such problems.

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

mohamed_zaki — Mon, 29 Sep 2014 07:25:24 GMT

Solution ---- (Repost):

data want (drop=newV );

retain newV ' ';

set have;

if _n_=1 and STATUS = ' ' then Do ;

STATUS = 'V';

newV=STATUS;

End;

Else if STATUS in ('C','V') then newV=STATUS;

Else if STATUS=' ' then STATUS=newV;

run;

Could you run this code on the data you have ? And let us know how it is difference from what you want. keeping in mind that the 'O' values for the STATUS is typo as you clarified in your previous post.

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

Nikos — Mon, 29 Sep 2014 08:20:15 GMT

Dear Mohamed,

Unfortunately it gives C across all observations plus it does not take into account the by-grouping

Thank you

Best regards

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

mohamed_zaki — Mon, 29 Sep 2014 08:39:44 GMT

I am running it in your sample data and it give me the desired output.

Without giving C across all observation. Even your data sample in the post is not starting with C and there is no way the code will give C for all the observation except for case when your data starting with C and not having V in any observation! the code is simple and you can trace it easily?

If you could share your entire code or upload the file you are reading from the data. in order to help you further.

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

RW9 — Mon, 29 Sep 2014 09:06:28 GMT

Hi,

Well, have only tried this on a small subset, and I think you are missing something in you logic. What if status=C and no other occurrence of C? Then its all missing. Please clarify your logic. This should be near to the OP (although I am not typing all that test data in):

data have;
infile datalines dsd missover dlm=",";
input status $ id1 $ id2 $ trade_qtr $ status_qtr $ price;
datalines;
,FG,5276,2009Q1,.,0,,
,FG,5276,2009Q2,.,0,,
V,FG,5276,2009Q3,2009Q3,0,
C,FG,5276,2009Q4,2009Q4,18508,
,FG,5276,2010Q1,.,0,,
,FG,5276,2010Q2,.,0,,
,FG,5276,2010Q3,.,0,,
,FG,5276,2010Q4,.,0,,
,FG,5276,2011Q1,.,0,,,
,FG,5276,2011Q2,.,0,,
,FG,5276,2011Q3,.,0,,
,FG,5276,2011Q4,.,
;
run;

data inter;
set have;
ind=input(compress(trade_qtr," Q"),best.);
run;

proc sql;
create table WANT as
select A.*,
          case when A.STATUS="C" and exists(select THIS.TRADE_QTR from WORK.INTER THIS where THIS.ID1=A.ID1 and THIS.ID2=A.ID2 and THIS.IND > A.IND and THIS.STATUS="C") then "C"
                when A.STATUS="C" and exists(select THIS.TRADE_QTR from WORK.INTER THIS where THIS.ID1=A.ID1 and THIS.ID2=A.ID2 and THIS.IND < A.IND and THIS.STATUS="C") then "V"
                when A.STATUS="" and not exists(select THIS.TRADE_QTR from WORK.INTER THIS where THIS.ID1=A.ID1 and THIS.ID2=A.ID2 and THIS.STATUS="C") then "V"
                end as NEW_STATUS
from    WORK.INTER A;
quit;

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

Nikos — Mon, 29 Sep 2014 09:48:27 GMT

Hi,

If STATUS='C" then prior observations get the value "V" and

forward observations get the value "C"

Thank you.

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

jakarman — Mon, 29 Sep 2014 11:44:59 GMT

Nikos, how big is your data? ( Say several millions of records some of several Gb's)

When it is really big work on an ordered dataset (reducing io)

When it is rather small, who cares you can probably doe something with a cartesian product with SQL

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

Nikos — Mon, 29 Sep 2014 12:08:38 GMT

Hi Jaap,

My data is about 2 million records

Thank you.

Best regards

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

gergely_batho — Mon, 29 Sep 2014 12:16:25 GMT

Have you tried my program? If data is already sorted, it needs only 1 (physical) pass through the data.

On the other hand it makes 2 logical passes. None of the solutions will work, that make only one logical pass through the data, because if a value is missing you should read ahead until the end of the group or until a nonmissing value.

Message was edited by: Gergely Bathó

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

jakarman — Mon, 29 Sep 2014 12:20:00 GMT

too little information for some choice for a technical solution. Missing is the dataset-sizing, how many Gb's.

As how big is your machine you are running on. (memory cpu's dasd).

The options are thinking in the logic.

- Getting that record with C isolated and remerged back applying a condition (That is what SQL will let you do)

- Working in the ordered dataset using IO-access the condition to be found by moving up an other PDW counter (double access)

- Using has tables where the condition key-value is stored in

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

Ksharp — Mon, 29 Sep 2014 12:25:40 GMT

Assuming there are at most one C in each id1 and id2 group .

data have;
input (STATUS     ID1     ID2     TRADE_QTR ) ($);
cards;
. FG     5276     2009Q1     
. FG     5276     2009Q2     
V     FG     5276     2009Q3     
C     FG     5276     2009Q4     
. FG     5276     2010Q1     
. FG     5276     2010Q2     
. FG     5276     2010Q3     
. FG     5276     2010Q4
. FG     5276     2011Q1     
. FG     5276     2011Q2     
. FG     5276     2011Q3     
. FG     5276     2011Q4     
. FG     5276     2012Q1     
. FG     5276     2012Q2     
. FG     5276     2012Q3     
. FG     5276     2012Q4     
. FG     5276     2013Q1     
. FG     5276     2013Q2     
. FG     5276     2013Q3     
. FG     5276     2013Q4     
. FG     5276     2014Q1     
. FG     5276     2014Q2     
. KL     4636186     2009Q1     
. KL     4636186     2009Q2     
;
run;
data want;
 set have;
 by  ID1     ID2;
 length _status $ 1;
 retain  _status;
 if first.id2 then _status='V' ;
 if status='C' then _status='C';
run;

Xia Keshan

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

Nikos — Mon, 29 Sep 2014 14:10:49 GMT

This is my Data Have and Want

It can only be one "C" per by group.

If there is "C" any prior obs become "V" and any subsequent remain "C"

If there is no "C" in a by group all obs should be "V"

Thank you

Data HAVE					Data WANT
Status	id1	ID2	status_qtr	price	Status	id1	ID2	status_qtr	price
V	83610	12	2010Q2	0	V	83610	12	2010Q2	0
C	83610	12	2010Q3	200	C	83610	12	2010Q3	200
	83610	12	2010Q4	0	C	83610	12	2010Q4	0
	83610	12	2011Q1	0	C	83610	12	2011Q1	0
	83610	12	2011Q2	0	C	83610	12	2011Q2	0
	83610	12	2011Q3	0	C	83610	12	2011Q3	0
	83610	12	2011Q4	0	C	83610	12	2011Q4	0
	83610	12	2012Q1	0	C	83610	12	2012Q1	0
	83610	12	2012Q2	0	C	83610	12	2012Q2	0
	83610	12	2012Q3	0	C	83610	12	2012Q3	0
	83610	12	2012Q4	0	C	83610	12	2012Q4	0
	83610	12	2013Q1	0	C	83610	12	2013Q1	0
	83610	12	2013Q2	0	C	83610	12	2013Q2	0
	83610	12	2013Q3	0	C	83610	12	2013Q3	0
	83610	12	2013Q4	0	C	83610	12	2013Q4	0
	83610	12	2014Q1	0	C	83610	12	2014Q1	0
	83610	12	2014Q2	0	C	83610	12	2014Q2	0
V	111070	12	2009Q1	2845	V	111070	12	2009Q1	2845
	111070	12	2009Q2	0	V	111070	12	2009Q2	0
C	111070	12	2009Q3	8000	C	111070	12	2009Q3	8000
	111070	12	2009Q4	0	C	111070	12	2009Q4	0
	111070	12	2010Q1	0	C	111070	12	2010Q1	0
	111070	12	2010Q2	0	C	111070	12	2010Q2	0
	111070	12	2010Q3	0	C	111070	12	2010Q3	0
	111070	12	2010Q4	0	C	111070	12	2010Q4	0
	111070	12	2011Q1	0	C	111070	12	2011Q1	0
	111070	12	2011Q2	0	C	111070	12	2011Q2	0
	111070	12	2011Q3	0	C	111070	12	2011Q3	0
	111070	12	2011Q4	0	C	111070	12	2011Q4	0
	111070	12	2012Q1	0	C	111070	12	2012Q1	0
	111070	12	2012Q2	0	C	111070	12	2012Q2	0
	111070	12	2012Q3	0	C	111070	12	2012Q3	0
	111070	12	2012Q4	0	C	111070	12	2012Q4	0
	111070	12	2013Q1	0	C	111070	12	2013Q1	0
	111070	12	2013Q2	0	C	111070	12	2013Q2	0
	111070	12	2013Q3	0	C	111070	12	2013Q3	0
	111070	12	2013Q4	0	C	111070	12	2013Q4	0
	111070	12	2014Q1	0	C	111070	12	2014Q1	0
	111070	12	2014Q2	0	C	111070	12	2014Q2	0
	187031	2	2010Q2	9000	V	187031	2	2010Q2	9000
	187031	2	2010Q3	0	V	187031	2	2010Q3	0
	187031	2	2010Q4	0	V	187031	2	2010Q4	0
	187031	2	2011Q1	0	V	187031	2	2011Q1	0
	187031	2	2011Q2	0	V	187031	2	2011Q2	0
	187031	2	2011Q3	0	V	187031	2	2011Q3	0
C	187031	2	2011Q4	0	C	187031	2	2011Q4	0
	187031	2	2012Q1	0	C	187031	2	2012Q1	0
	187031	2	2012Q2	0	C	187031	2	2012Q2	0
	187031	2	2012Q3	0	C	187031	2	2012Q3	0
	187031	2	2012Q4	0	C	187031	2	2012Q4	0
	187031	2	2013Q1	0	C	187031	2	2013Q1	0
	187031	2	2013Q2	0	C	187031	2	2013Q2	0
	187031	2	2013Q3	0	C	187031	2	2013Q3	0
	187031	2	2013Q4	0	C	187031	2	2013Q4	0
	187031	2	2014Q1	0	C	187031	2	2014Q1	0
	187031	2	2014Q2	0	C	187031	2	2014Q2	0
V	141887	7	2010Q3	0	V	141887	7	2010Q3	0
	141887	7	2010Q4	200	V	141887	7	2010Q4	200
	141887	7	2011Q1	3091	V	141887	7	2011Q1	3091
	141887	7	2011Q2	0	V	141887	7	2011Q2	0
	141887	7	2011Q3	0	V	141887	7	2011Q3	0
	141887	7	2011Q4	0	V	141887	7	2011Q4	0
V	141887	7	2012Q1	0	V	141887	7	2012Q1	0
	141887	7	2012Q2	0	V	141887	7	2012Q2	0
	141887	7	2012Q3	0	V	141887	7	2012Q3	0
	141887	7	2012Q4	0	V	141887	7	2012Q4	0
C	141887	7	2013Q1	0	C	141887	7	2013Q1	0
	141887	7	2013Q2	0	C	141887	7	2013Q2	0
	141887	7	2013Q3	0	C	141887	7	2013Q3	0
	141887	7	2013Q4	0	C	141887	7	2013Q4	0
	141887	7	2014Q1	0	C	141887	7	2014Q1	0
	141887	7	2014Q2	0	C	141887	7	2014Q2	0
C	141887	9	2010Q4	200	C	141887	9	2010Q4	200
	141887	9	2011Q1	0	C	141887	9	2011Q1	0
	141887	9	2011Q2	0	C	141887	9	2011Q2	0
	141887	9	2011Q3	0	C	141887	9	2011Q3	0
	141887	9	2011Q4	0	C	141887	9	2011Q4	0
	141887	9	2012Q1	0	C	141887	9	2012Q1	0
	141887	9	2012Q2	0	C	141887	9	2012Q2	0
	141887	9	2012Q3	0	C	141887	9	2012Q3	0
	141887	9	2012Q4	0	C	141887	9	2012Q4	0
	141887	9	2013Q1	0	C	141887	9	2013Q1	0
	141887	9	2013Q2	0	C	141887	9	2013Q2	0
	141887	9	2013Q3	0	C	141887	9	2013Q3	0
	141887	9	2013Q4	0	C	141887	9	2013Q4	0
	141887	9	2014Q1	0	C	141887	9	2014Q1	0
	141887	9	2014Q2	0	C	141887	9	2014Q2	0
V	7891	1	2009Q1	0	V	7891	1	2009Q1	0
	7891	1	2009Q2	0	V	7891	1	2009Q2	0
	7891	1	2009Q3	0	V	7891	1	2009Q3	0
	7891	1	2009Q4	0	V	7891	1	2009Q4	0
V	7891	1	2010Q1	0	V	7891	1	2010Q1	0
V	7891	1	2010Q2	0	V	7891	1	2010Q2	0
C	7891	1	2010Q3	0	C	7891	1	2010Q3	0
	7891	1	2010Q4	0	C	7891	1	2010Q4	0
	7891	1	2011Q1	0	C	7891	1	2011Q1	0
	7891	1	2011Q2	0	C	7891	1	2011Q2	0
	7891	1	2011Q3	0	C	7891	1	2011Q3	0
	7891	1	2011Q4	0	C	7891	1	2011Q4	0
	7891	1	2012Q1	0	C	7891	1	2012Q1	0
	7891	1	2012Q2	0	C	7891	1	2012Q2	0
	7891	1	2012Q3	0	C	7891	1	2012Q3	0
	7891	1	2012Q4	0	C	7891	1	2012Q4	0
	7891	1	2013Q1	0	C	7891	1	2013Q1	0
	7891	1	2013Q2	0	C	7891	1	2013Q2	0
	7891	1	2013Q3	0	C	7891	1	2013Q3	0
	7891	1	2013Q4	0	C	7891	1	2013Q4	0
	7891	1	2014Q1	0	C	7891	1	2014Q1	0
	7891	1	2014Q2	0	C	7891	1	2014Q2	0
	4282222	2	2009Q1	10314	V	4282222	2	2009Q1	10314
	4282222	2	2009Q2	0	V	4282222	2	2009Q2	0
	4282222	2	2009Q3	0	V	4282222	2	2009Q3	0
V	4282222	2	2009Q4	0	V	4282222	2	2009Q4	0
	4282222	2	2010Q1	0	V	4282222	2	2010Q1	0
	4282222	2	2010Q2	0	V	4282222	2	2010Q2	0
	4282222	2	2010Q3	0	V	4282222	2	2010Q3	0
	4282222	2	2010Q4	555	V	4282222	2	2010Q4	555
	4282222	2	2011Q1	0	V	4282222	2	2011Q1	0
	4282222	2	2011Q2	0	V	4282222	2	2011Q2	0
	4282222	2	2011Q3	0	V	4282222	2	2011Q3	0
	4282222	2	2011Q4	0	V	4282222	2	2011Q4	0
	4282222	2	2012Q1	0	V	4282222	2	2012Q1	0
	4282222	2	2012Q2	0	V	4282222	2	2012Q2	0
	4282222	2	2012Q3	0	V	4282222	2	2012Q3	0
	4282222	2	2012Q4	0	V	4282222	2	2012Q4	0
	4282222	2	2013Q1	0	V	4282222	2	2013Q1	0
	4282222	2	2013Q2	0	V	4282222	2	2013Q2	0
	4282222	2	2013Q3	0	V	4282222	2	2013Q3	0
	4282222	2	2013Q4	0	V	4282222	2	2013Q4	0
	4282222	2	2014Q1	0	V	4282222	2	2014Q1	0
	4282222	2	2014Q2	0	V	4282222	2	2014Q2	0
	82522	3	2011Q4	2558	C	82522	3	2011Q4	2558
	82522	3	2012Q1	0	C	82522	3	2012Q1	0
	82522	3	2012Q2	0	C	82522	3	2012Q2	0
	82522	3	2012Q3	0	C	82522	3	2012Q3	0
	82522	3	2012Q4	0	C	82522	3	2012Q4	0
	82522	3	2013Q1	0	C	82522	3	2013Q1	0
C	82522	3	2013Q2	0	C	82522	3	2013Q2	0
	82522	3	2013Q3	0	C	82522	3	2013Q3	0
	82522	3	2013Q4	0	C	82522	3	2013Q4	0
	82522	3	2014Q1	0	C	82522	3	2014Q1	0
	82522	3	2014Q2	0	C	82522	3	2014Q2	0

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

Nikos — Mon, 29 Sep 2014 14:11:27 GMT

Please see my reply to RW9

Thank you

Re: By Group -- Replace MISSING VALUES ALONG CONSECUTIVE DATES - (Repost)

mohamed_zaki — Mon, 29 Sep 2014 21:21:18 GMT

data want (drop=newV );

set have;

by id1 id2;

if (first.id2 ) then do;

retain newV ' ';

if STATUS = ' ' then Do ;

STATUS = 'V';

newV=STATUS;

End;

Else if STATUS in ('C','V') then newV=STATUS;

End;

Else do;

If STATUS in ('C','V') then newV=STATUS;

Else if STATUS=' ' then STATUS=newV;

end;

run;