BookmarkSubscribeRSS Feed
Nieves
Quartz | Level 8

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!

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Nieves
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Nieves
Quartz | Level 8

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;

 

FredrikE
Rhodochrosite | Level 12

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;

Nieves
Quartz | Level 8

Thanks @FredrikE It works!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2592 views
  • 3 likes
  • 4 in conversation