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
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;
if count<5 then description='Other' ?
Or would you like to group all the ones with less than 5 individually together into one 'Other'?
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
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;
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
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.
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.
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.