BookmarkSubscribeRSS Feed
ali_far
Obsidian | Level 7

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

6 REPLIES 6
ChrisNZ
Tourmaline | Level 20

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

ali_far
Obsidian | Level 7
Thanks, It is useful. However I decided to restructure the data set for further analysis
PeterClemmensen
Tourmaline | Level 20

What do you want your desired result to look like?

jklaverstijn
Rhodochrosite | Level 12

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.

ali_far
Obsidian | Level 7

Hi,

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

Regards,

Astounding
PROC Star

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.

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
  • 6 replies
  • 921 views
  • 2 likes
  • 5 in conversation