fill in missing value by group and time

Reply
Occasional Contributor
Posts: 15

fill in missing value by group and time

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?

grouptimevalue
1Jan.
1Feb10
1Mar.
2Jan.
2Feb.
2Mar20
3Jan30
3Feb.
3Mar.
4Jan.
4Feb40
4Mar.
5Jan50
5Feb.
5Mar.

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

grouptimevalue
1Jan10
1Feb10
1Mar10
2Jan20
2Feb20
2Mar20
3Jan30
3Feb30
3Mar30
4Jan40
4Feb40
4Mar40
5Jan50
5Feb50
5Mar50

 

 

Thank you!

Super User
Super User
Posts: 7,401

Re: fill in missing value by group and time

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.

Occasional Contributor
Posts: 15

Re: fill in missing value by group and time

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:

error.JPG

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

 

Thanks

Super User
Super User
Posts: 7,401

Re: fill in missing value by group and time

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.

Occasional Contributor
Posts: 15

Re: fill in missing value by group and time

[ Edited ]

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;

 

Super User
Posts: 6,938

Re: fill in missing value by group and time

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

 

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 120

Re: fill in missing value by group and time

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

 

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;

Occasional Contributor
Posts: 15

Re: fill in missing value by group and time

Thanks @FredrikE It works!

Ask a Question
Discussion stats
  • 7 replies
  • 94 views
  • 2 likes
  • 4 in conversation