BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JoannaL
Fluorite | Level 6

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

7 REPLIES 7
Reeza
Super User

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;
JoannaL
Fluorite | Level 6

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;

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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...

Astounding
PROC Star

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;

 

FreelanceReinh
Jade | Level 19

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.

Tom
Super User Tom
Super User

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;

 

JoannaL
Fluorite | Level 6

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! 

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
  • 1447 views
  • 2 likes
  • 6 in conversation