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!
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.
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
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:
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.
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;
POST.YOUR.DATA.IN.A.DATA.STEP.
Without that, anything we give you will be just guesswork.
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;
Thanks @FredrikE It works!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.