SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Rename fields with low frequency

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Rename fields with low frequency

 

I am looking for edit the data GiftOriginal.

 

In the column Toy where a observation appear less then 3  times(Bicycle and Plane) I want to rename "diverse".

 

The data ManuallyFormated is what I am looking for as a result.

 

Any idea how I can do that?

 


data GiftOriginal;
input ID$ toy$ town$;
cards;
Jensen Doodle Columbus
Tom Car Dayton
Mathew Truck Columbus
Zhin Doodle Columbus
Gylbert Car Columbus
Luke Bicycle Columbus
Barbara Doodle Columbus
Judy Doodle Dayton
Dennis Truck Dayton
Anthony Car Dayton
Ian Plane Dayton
Ruan Car Dayton
Santiago Truck Dayton
Uin Doodle Columbus
Josh Car Columbus
run;

data ManuallyFormated;
input ID$ toy$ town$;
cards;

Jensen Doodle Columbus
Tom Car Dayton
Mathew Truck Columbus
Zhin Doodle Columbus
Gylbert Car Columbus
Luke DIVERSE Columbus
Barbara Doodle Columbus
Judy Doodle Dayton
Dennis Truck Dayton
Anthony Car Dayton
Ian DIVERSE Dayton
Ruan Car Dayton
Santiago Truck Dayton
Uin Doodle Columbus
Josh Car Columbus

run;

 

Attachment

Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 8,190

Re: Rename fields with low frequency

Yes, I edited my post.  It should work with that data though you would need to change B.CNT < 3 to be B.CNT <= 3 for your logic.

View solution in original post


All Replies
Super User
Super User
Posts: 8,190

Re: Rename fields with low frequency

[ Edited ]

Ok,didnt see the less than 3 part, try:

data GiftOriginal;
  input ID$ toy$ town$;
cards;
Jensen Doodle Columbus
Tom Car Dayton
Mathew Truck Columbus
Zhin Doodle Columbus
Gylbert Car Columbus
Luke Bicycle Columbus
Barbara Doodle Columbus
Judy Doodle Dayton
Dennis Truck Dayton
Anthony Car Dayton
Ian Plane Dayton
Ruan Car Dayton
Santiago Truck Dayton
Uin Doodle Columbus
Josh Car Columbus
run;

proc sql;
  create table WANT as 
  select A.ID,
         case when B.CNT < 3 then "DIVERSE" else A.TOY end as TOY,
         A.TOWN
  from   GIFTORIGINAL A
  left join (select TOY,count(TOY) as CNT from GIFTORIGINAL group by TOY) B
  on     A.TOY=B.TOY;
quit;
Contributor
Posts: 23

Re: Rename fields with low frequency

Hi RW9,

 

I edited the sample data to clarify.

If the toy appears once(plane), twice(bicycle), and or 3 times(truck) I would like to format and rename to diverse:

 

data GiftOriginal;
input ID$ toy$ town$;
cards;
Jensen Doodle Columbus
Tom Car Dayton
Mathew Truck Columbus
Zhin Doodle Columbus
Gylbert Car Columbus
Luke Bicycle Columbus
Barbara Doodle Columbus
Judy Doodle Dayton
Dennis Truck Dayton
Anthony Car Dayton
Ian Plane Dayton
Ruan Car Dayton
Santiago Truck Dayton
Uin Doodle Columbus
Josh Car Columbus
Bob Bicycle Columbus
run;

 

 

data ManuallyFormated;
input ID$ toy$ town$;
cards;

Jensen Doodle Columbus
Tom Car Dayton
Mathew DIVERSE Columbus
Zhin Doodle Columbus
Gylbert Car Columbus
Luke DIVERSE Columbus
Barbara Doodle Columbus
Judy Doodle Dayton
Dennis DIVERSE Dayton
Anthony Car Dayton
Ian DIVERSE Dayton
Ruan Car Dayton
Santiago DIVERSE Dayton
Uin Doodle Columbus
Josh Car Columbus
Bob  DIVERSE Columbus


run;

 

 

Solution
3 weeks ago
Super User
Super User
Posts: 8,190

Re: Rename fields with low frequency

Yes, I edited my post.  It should work with that data though you would need to change B.CNT < 3 to be B.CNT <= 3 for your logic.

Contributor
Posts: 23

Re: Rename fields with low frequency

RW9,

 

Thanks a lot.

 

You are great!!!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 139 views
  • 0 likes
  • 2 in conversation