07-12-2017 03:26 AM
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?
and I want the data to be filled in as follows:
07-12-2017 04:03 AM
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.
07-12-2017 04:24 AM
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?
07-12-2017 04:42 AM
This is why we ask for a datastep of your code, this link shows you how to do it;
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:
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.
07-12-2017 07:06 AM - edited 07-12-2017 07:10 AM
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;
07-12-2017 04:42 AM
07-12-2017 05:38 AM
Try this, you need to change the second step to fit your existing code, but if I understand you correct, this might work
length group 8 time $3 value 8;
input 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 .
create table test2 as
select group, time, max(value) as value
group by 1;