topic fill in missing value by group and time in SAS Programming

fill in missing value by group and time

Nieves — Wed, 12 Jul 2017 07:26:32 GMT

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!

Re: fill in missing value by group and time

RW9 — Wed, 12 Jul 2017 08:03:28 GMT

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.

Re: fill in missing value by group and time

Nieves — Wed, 12 Jul 2017 08:24:55 GMT

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

Re: fill in missing value by group and time

RW9 — Wed, 12 Jul 2017 08:42:12 GMT

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-data-AKA-generate/ta-p/258712

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.

Re: fill in missing value by group and time

Kurt_Bremser — Wed, 12 Jul 2017 08:42:31 GMT

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

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

Re: fill in missing value by group and time

FredrikE — Wed, 12 Jul 2017 09:38:08 GMT

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;

Re: fill in missing value by group and time

Nieves — Wed, 12 Jul 2017 11:10:25 GMT

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;

Re: fill in missing value by group and time

Nieves — Wed, 12 Jul 2017 11:09:23 GMT

Thanks @FredrikE It works!