fill in missing value by group and time

[Nieves]

Hello

may I ask what if the non missing value does not appear as the first observation by group and time? How to fill in the table in the following case?

group	time	value
1	Jan	.
1	Feb	10
1	Mar	.
2	Jan	.
2	Feb	.
2	Mar	20
3	Jan	30
3	Feb	.
3	Mar	.
4	Jan	.
4	Feb	40
4	Mar	.
5	Jan	50
5	Feb	.
5	Mar	.

and I want the data to be filled in as follows:

group	time	value
1	Jan	10
1	Feb	10
1	Mar	10
2	Jan	20
2	Feb	20
2	Mar	20
3	Jan	30
3	Feb	30
3	Mar	30
4	Jan	40
4	Feb	40
4	Mar	40
5	Jan	50
5	Feb	50
5	Mar	50

Thank you!

[RW9]

Not tested this code - note Post test data in the form of a datastep as asked before!

proc sql;
  create table WANT as
  select A.GROUP,
             A.TIME,
             B.VALUE
  from    HAVE A
  left join (select distinct VALUE from HAVE where VALUE ne .) B
  on       A.GROUP=B.GROUP;
quit;

Also, what happens if there is more than one result per group?  As a tip, just having month is not useful, year at minimum here is required.

[Nieves]

Thanks @RW9 for your advice. There is only one nonmissing value for each group. my data is monthly and therefore is indexed by month 1-1000, using Jan, Feb, Mar is a simplification. 

I have tried the datastep method to create sample data but failed to do so. Sorry about it as I am very new to SAS. 

By the way, I have tried your code and the following error message shows up:

Would you please kindly advice on what to do with it?

Thanks

[RW9]

This is why we ask for a datastep of your code, this link shows you how to do it;

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

I can only go on what I see in the post - I can't see your screen!  So you put in your post the data looks like:

group

time

value

Therefore in my code I used variables called group, time, value.  If these aren't in your data then of course you will get errors, but I can't see your your data so I cannot know this?

For this point:
"my data is monthly and therefore is indexed by month 1-1000, using Jan, Feb, Mar is a simplification. "  - irrelevant, month on its own is not substantial enough to assume anything, year is minimum required information to make the datapoint valid - i.e. later on if someone else comes in not knowing what you know then the data is meaningless without year.

[Nieves]

I have modified your code a little bit and it works! @RW9

proc sql;
  create table WANT as
  select A.GROUP,
             A.TIME,
             B.VALUE
  from    HAVE A
  left join (select distinct GROUP, VALUE from HAVE where VALUE ne .) B
  on       A.GROUP=B.GROUP;
quit;

[Kurt_Bremser]

POST.YOUR.DATA.IN.A.DATA.STEP.

Without that, anything we give you will be just guesswork.

[FredrikE]

Try this, you need to change the second step to fit your existing code, but if I understand you correct, this might work 🙂

data test;

length group 8 time $3 value 8;

input group time value;

datalines;

1 Jan .

1 Feb 10

1 Mar .

2 Jan .

2 Feb .

2 Mar 20

3 Jan 30

3 Feb .

3 Mar .

4 Jan .

4 Feb 40

4 Mar .

5 Jan 50

5 Feb .

5 Mar .

;

run;

proc sql;

create table test2 as

select group, time, max(value) as value

from test

group by 1;

quit;

[Nieves]

Thanks @FredrikE It works!