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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.