topic Re: data step rule does not work for only one row in SAS Programming

data step rule does not work for only one row

JoannaL — Fri, 27 May 2016 04:38:51 GMT

Hi, I'm using SAS 9.3.

I generated frequencies of a variable "dxn" by ID using proc freq (frequencies generated attached).

I only want to keep the frequency of dxn when it is 1 but I have a few IDs with 100% dxn=0. I tried changing this by assigning dxn=1 and percent=0 whenever dxn=0 and percent=100. This worked for all rows except one (ID=78). I have tried debugging and SAS does acknowledge that dxn=0 and precent=100 for ID 78 but it does not change the values as requested. Why is this happenning??

data freq2;
set freq;
if dxn=0 and percent=100 then do; 
dxn=1;
percent=0;
drop if dxn=0;
end;

Re: data step rule does not work for only one row

Reeza — Fri, 27 May 2016 05:02:52 GMT

Your using the drop statement incorrectly.

Drop refers to to dropping a variable not an observation.

If you want to delete an observation use delete.

If dxn=0 then delete;

I'm not 100% sure what you want though, is it to see 0/1 for all IDs? If so try the SPARSE option in your proc freq and then delete the 0

Proc freq data=have;
Table id*dxn / out =want (where =(dxn=1)) sparse;
Run;

Re: data step rule does not work for only one row

JoannaL — Fri, 27 May 2016 06:37:38 GMT

Thanks Reeza, I just want the 1s and your solution will give me the 1s. Yeah I made a mistake and copied a wrong line of code. It was supposed to be if dxn=0 then delete.

However, it doesn't answer why there is a chance that a data step works for some rows and not for others. This seems to imply that other datasets may be wrongly processed too.

i.e. from my code it is clear that dxn will be changed to 1 and percent changed to 0 whenever dxn=0 and percent=100. But if you try running the code, row 78 which meets the criteria does not process and stays as dxn=0, percent=100.

data freq2;
set freq;
if dxn=0 and percent=100 then do; 
dxn=1;
percent=0;
end;

Re: data step rule does not work for only one row

RW9 — Fri, 27 May 2016 08:46:41 GMT

Could it be a rounding issue? There are times when a calculation results in a value which is very small, say:

pcent=100.00000000000001

It can be that you don't see that really small part (there are other posts here that go into detail as to why this happens), but when you do logical checks 100 != 100.0000000000001, so it fails. I would suggest you always use a rounding in these cases:

if dxn=0 and floor(percent)=100...

Re: data step rule does not work for only one row

Astounding — Fri, 27 May 2016 10:56:04 GMT

I'm not sure you need to change dxn to 1 when 0 is the accurate value. This should work:

data freq2;

set freq;

if (dxn=1) or (dxn=0 and percent=100);

run;

Re: data step rule does not work for only one row

FreelanceReinh — Fri, 27 May 2016 11:09:19 GMT

Hi @JoannaL,

I second RW9's idea that this is most likely a rounding issue. You can check this, if you like, with the following PROC FREQ step:

proc freq data=freq;
where percent>99.99;
format percent hex16.;
tables percent;
run;

Any values of PERCENT other than 4059000000000000 (hexadecimal floating-point representations) in the output of this step would represent numbers different from 100.

Using the FLOOR function, as suggested by RW9, might work (due to implied fuzzing). Personally, I prefer the ROUND function. For your data this should work:

if dxn=0 and round(percent, .001)=100 then do;

Or, even simpler:

if dxn=0 and percent>99.99 then do;

Also, I would recommend to make sure that the absolute numbers in variable Frequency (if they are kept in dataset FREQ2) are consistent with the modified DXN values.

Edit: Removed the remark about the DELETE statement after realizing that you removed it from the DO-END block where it was (as "drop ...") in your initial post.

Re: data step rule does not work for only one row

Tom — Fri, 27 May 2016 11:13:01 GMT

Why not take a step back and ask the question. What are you actually trying to do?

If DXn is a 1/0 variable and what you want is the COUNT and PERCENT of records that are 1 per ID value you can do it in one step from your original data by using PROC MEANS (summary).

proc summary data=have nway ;
   class id ;
   var dxn ;
   output out=want sum=count mean=percent ;
run;

Re: data step rule does not work for only one row

JoannaL — Fri, 03 Jun 2016 04:14:28 GMT

Hi everyone,

Thanks for the answers. I've finally found the time to test them out and they all work.
It was a rounding issue after all and all the solutions provided with that gave me what I wanted to do with the codes.

Tom suggested going a step back and yes, Reeza has actually suggested a solution to what I originally intended.

So, thanks again everyone!