topic Re: do loop over the same observations in SAS Programming

do loop over the same observations

ali_far — Thu, 28 Dec 2017 07:15:35 GMT

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

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

Re: do loop over the same observations

ChrisNZ — Thu, 28 Dec 2017 08:24:40 GMT

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

Re: do loop over the same observations

PeterClemmensen — Thu, 28 Dec 2017 10:52:29 GMT

What do you want your desired result to look like?

Re: do loop over the same observations

jklaverstijn — Thu, 28 Dec 2017 11:10:14 GMT

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.

Re: do loop over the same observations

Astounding — Thu, 28 Dec 2017 11:19:58 GMT

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.

Re: do loop over the same observations

ali_far — Fri, 29 Dec 2017 06:42:50 GMT

Hi,

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

Regards,

Re: do loop over the same observations

ali_far — Fri, 29 Dec 2017 06:44:05 GMT

Thanks, It is useful. However I decided to restructure the data set for further analysis