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

Hi Team,

I have a dataset and did a proc freq of Code*description and i get what is shown below

I want to put OTHER in the description column for those which have a count of 5 or less

I should get like shown below if I did the freq of the dataset

Code  Description Freq Count

000        OTHER               20

I have several hunderds which have count less than 5

I cannot write IF condition for several hunderds in the parent dataset like:

if Description in ("Urinary", "Heart" ,"nose" "head"," hand"," finger"," toes") then do;

Description="Other";

code="000";

end;

run;

Please help me with the best solution

Code    Decription     freq count

100       pain                 15

101        urinary            3

102        digestive        33

103        heart              2

104        nerves           55

105        nose              1

106        head               4

107       leg                  45

108       hand                2

109       finger               5

110       toes                 3

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Then use the proc freq output to feed a lookup table so its dynamic and you aren't using an if.

ie

proc freq data=have;

table code*description/out=list_format;

run;

data list_format;

set list_format;

if count<= 5 then do;

new_description='Other';

new_code=999; *so it will sort last;

end;

else do;

      new_description=description

     new_code=code;

end;

drop count;

run;

proc sort data=have; by code description;

proc sort data=list_fmt; by code description;

data want;

merge have list_fmt;

by code description;

run;

View solution in original post

5 REPLIES 5
Reeza
Super User

if count<5 then description='Other' ?

Or would you like to group all the ones with less than 5 individually together into one 'Other'?

robertrao
Quartz | Level 8

Yes. I would like to group all the ones with 5 and less than 5 into one "OTHER"

AND also what you see is not my actual dataset.Its the dataset after I did the freq......

I need to make these changes in my Parent dataset.

I just did a freq to see how many are less than 5!!!

AND I HAVE SEVERAL HUNDREDS OF THOSE WITH LESS THAN 5 Making it difficult for me to use the IF condition

Regards

Reeza
Super User

Then use the proc freq output to feed a lookup table so its dynamic and you aren't using an if.

ie

proc freq data=have;

table code*description/out=list_format;

run;

data list_format;

set list_format;

if count<= 5 then do;

new_description='Other';

new_code=999; *so it will sort last;

end;

else do;

      new_description=description

     new_code=code;

end;

drop count;

run;

proc sort data=have; by code description;

proc sort data=list_fmt; by code description;

data want;

merge have list_fmt;

by code description;

run;

robertrao
Quartz | Level 8

Thanks a lot.

I understood the logic.

So after merging the datasets we consider the new_code and new_description for Proc Freq purpose RIGHT????

Secondly,

When we converted to new_code code which was 001 is getting translated to 1(no leading zeros)

Is there any way i could retain the leading zeros???

Thanks

LinusH
Tourmaline | Level 20

New_code is defined as numeric in the example.

For printing with leading zeros in a report, use z. format.

Otherwise, define it as char instead.

Data never sleeps

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
  • 5 replies
  • 846 views
  • 0 likes
  • 3 in conversation