DATA Step, Macro, Functions and more

do loop over the same observations

Reply
Occasional Contributor
Posts: 16

do loop over the same observations

Hi all,

I am trying to do loop over the same observations in a big dataset. I have used existing codes in the communities , however, they did not work.

here is a part of dataset:

 

Id   year1        year2          year3

1      20               .                .

1        .               30             .

1        .                .                15

2       20               .               .

2                        20

2                                         20

3

.

.       

 I want to apply some conditions , for example: 

data want;
do until (last.Id);
set have ;
by id;
if year1=year2=year3 then delete;end; run;

 what is the problem with this code?

your help will be appreciated

PROC Star
Posts: 2,223

Re: do loop over the same observations

2 problems at least:

 

1. The set statement will keep the latest values, so  .  .  15 for ID=1

  You can try to use UPDATE instead to ignore missing values.

  Maybe you can replace the set statement with (I never used UPDATE in a do loop so that's a test) :

   update HAVE(obs=0) HAVE;

  You dont need a loop really. Using the above statement without a do loop and testing end of group with function lag() would work for sure.

 Another option is to store the non-missing values in different, retained, variables.

 

2. The test  year1=year2=year3 should be year1=year2 and year2=year3

Occasional Contributor
Posts: 16

Re: do loop over the same observations

Thanks, It is useful. However I decided to restructure the data set for further analysis
PROC Star
Posts: 1,190

Re: do loop over the same observations

What do you want your desired result to look like?

Valued Guide
Posts: 530

Re: do loop over the same observations

Reconsider your data model. Having the years in the columns is why you have this challenge. A normalized table would have three columns: id, year and value. This would make any logic or reporting much easier. And just imagine what would be the consequence if you  have to add another year. Look at proc transpose or diy a datastep. 

 

Hope this helps,

-- Jan.

Occasional Contributor
Posts: 16

Re: do loop over the same observations

Posted in reply to jklaverstijn

Hi,

Thanks, you are right. I have restructured the data set. Now it is even easier for the further analysis.

Regards,

Super User
Posts: 6,537

Re: do loop over the same observations

If the intent is to keep ID=1, but delete ID=2, while getting all the data onto a single observation for each ID, you could try:

 

data want;

update have (obs=0) have;

by ID;

if year1=year2=year3 then delete;

run;

 

If that's not the intent, you will need to spell it out.  We can't really figure out why the code doesn't work if we don't know what it is supposed to accomplish.

Ask a Question
Discussion stats
  • 6 replies
  • 193 views
  • 2 likes
  • 5 in conversation