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;
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.
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;
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;
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.
RW9,
Thanks a lot.
You are great!!!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.