topic Re: Delete Columns with certain characters in SAS Programming

Delete Columns with certain characters

michokwu — Wed, 14 Oct 2020 18:51:58 GMT

Hello Experts,

I have a dataset with over 100 columns. I would like to create a macro that contains all the variable names then drop the ones where the value is null'.

Please see attached sample set.

Names	tropical_fruit	whole_milk	pip_fruit	other_vegetables	rolls_buns	pot_plants	citrus_fruit	beef	frankfurter	chicken	butter	fruit_vegetable_juice	packaged_fruit_vegetables	chocolate	specialty_bar	butter_milk	bottled_water	yogurt	sausage	brown_bread	hamburger_meat	root_vegetables	pork	pastry	canned_beer	berries	coffee	misc__beverages	ham	turkey	curd_cheese	red_blush_wine	frozen_potato_products	flour	sugar	frozen_meals	herbs	soda	detergent	grapes	processed_cheese	fish	sparkling_wine	newspapers	curd	pasta	popcorn	finished_products	beverages	bottled_beer	dessert	dog_food	specialty_chocolate	condensed_milk	cleaner	white_wine	meat	ice_cream	hard_cheese	cream_cheese	liquor	pickled_vegetables	liquor__appetizer_	UHT_milk	candy	onions	hair_spray	photo_film	domestic_eggs	margarine	shopping_bags	salt	oil	whipped_sour_cream	frozen_vegetables	sliced_cheese	dish_cleaner	baking_powder	specialty_cheese	salty_snack	Instant_food_products	pet_care	white_bread	female_sanitary_products	cling_film_bags	soap	frozen_chicken	house_keeping_products	spread_cheese	decalcifier	frozen_dessert	vinegar	nuts_prunes	potato_products	frozen_fish	hygiene_articles	artif__sweetener	light_bulbs	canned_vegetables	chewing_gum	canned_fish	cookware	semi_finished_bread	cat_food	bathroom_cleaner	prosecco	liver_loaf	zwieback	canned_fruit	frozen_fruits	brandy	baby_cosmetics	spices	napkins	waffles	sauces	rum	chocolate_marshmallow	long_life_bakery_product	bags	sweet_spreads	soups	mustard	specialty_fat	instant_coffee	snack_products	organic_sausage	soft_cheese	mayonnaise	dental_care	roll_products	kitchen_towels	flower_soil_fertilizer	cereals	meat_spreads	dishes	male_cosmetics	candles	whisky	tidbits	cooking_chocolate	seasonal_products	liqueur	abrasive_cleaner	syrup	ketchup	cream	skin_care	rubbing_alcohol	nut_snack	cocoa_drinks	softener	organic_products	cake_bar	honey	jam	kitchen_utensil	flower__seeds_	rice	tea	salad_dressing	specialty_vegetables	pudding_powder	ready_soups	make_up_remover	toilet_cleaner	preservation_products
Member_number	1808	2552	2300	1187	4941	3803	null	2193	1997	4546	4736	1959	1974	2421	1513	2810	4976	4056	3611	4286	4783	3709	4289	2900	3527	3841	4272	1697	2978	2910	1377	4162	4875	4750	1199	1603	2022	1202	4389	3746	2623	1024	4828	3564	3221	4699	2184	3783	1923	3795	3922	4187	2904	1222	4182	4389	4262	4494	1226	1395	2794	1547	3495	2836	1311	2447	2879	4651	4645	1765	3429	4818	3379	4186	4217	1743	1363	2525	1656	2215	1746	3571	3950	2256	3120	3206	4691	1632	1099	2762	2122	1800	3012	3579	2340	4532	1273	2467	4088	2336	1780	4891	1630	3512	3211	2203	3186	1968	4301	4533	1924	2070	3324	2086	2250	4323	4940	4088	1899	1450	1047	2262	2687	2290	4392	3634	3511	1356	3677	4172	4734	3776	3385	1326	3663	2100	2181	2893	1654	4335	4351	1149	4525	2421	1323	2476	2690	1540	4495	null	2560	2530	2855	null	3783	2005	1748	4201	3972	1373	2064	3894	1100	2913	null	2484	1529
Date	21-07-2015	5/1/2015	19-09-2015	12/12/2015	14-02-2015	23-12-2015	null	14-04-2015	21-07-2015	3/9/2015	21-07-2015	30-03-2015	3/5/2015	2/9/2015	3/8/2015	8/9/2015	17-07-2015	12/6/2015	13-02-2015	8/3/2015	22-10-2015	26-10-2015	8/10/2015	11/4/2015	29-09-2015	19-07-2015	6/6/2015	21-05-2015	5/6/2015	28-07-2015	14-03-2015	22-12-2015	12/5/2015	21-09-2015	10/12/2015	30-09-2015	4/3/2015	22-07-2015	20-10-2015	17-08-2015	10/10/2015	10/8/2015	8/8/2015	27-06-2015	13-01-2015	8/2/2015	30-06-2015	17-06-2015	14-02-2015	17-01-2015	27-02-2015	9/5/2015	6/6/2015	24-09-2015	12/5/2015	13-04-2015	16-10-2015	6/1/2015	13-04-2015	7/3/2015	16-09-2015	18-04-2015	27-06-2015	29-12-2015	12/2/2015	28-12-2015	18-04-2015	21-06-2015	25-07-2015	11/3/2015	4/1/2015	11/1/2015	21-01-2015	25-10-2015	18-12-2015	18-04-2015	21-06-2015	24-05-2015	30-10-2015	22-07-2015	15-02-2015	11/12/2015	1/10/2015	11/4/2015	20-10-2015	11/8/2015	12/10/2015	29-05-2015	13-08-2015	7/11/2015	7/4/2015	31-07-2015	3/5/2015	17-08-2015	7/10/2015	29-11-2015	4/5/2015	18-04-2015	23-09-2015	14-04-2015	13-10-2015	19-04-2015	22-05-2015	17-11-2015	30-09-2015	27-06-2015	25-11-2015	10/1/2015	20-12-2015	21-05-2015	2/3/2015	19-12-2015	6/6/2015	2/6/2015	15-10-2015	11/10/2015	21-08-2015	5/3/2015	19-10-2015	16-11-2015	9/8/2015	4/11/2015	14-09-2015	3/7/2015	13-08-2015	2/12/2015	15-11-2015	2/4/2015	21-04-2015	8/10/2015	12/7/2015	17-05-2015	22-06-2015	7/2/2015	4/1/2015	19-09-2015	23-06-2015	12/12/2015	25-03-2015	5/12/2015	8/5/2015	12/12/2015	11/5/2015	21-11-2015	3/11/2015	4/12/2015	20-10-2015	26-11-2015	8/4/2015	null	19-06-2015	13-06-2015	5/8/2015	null	22-06-2015	22-09-2015	14-08-2014	6/8/2014	4/5/2014	28-04-2014	6/4/2014	10/2/2014	12/3/2015	4/7/2015	null	17-08-2014	1/3/2014
Amount	25	26	27	28	29	30	null	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75	76	77	78	79	80	81	82	83	84	85	86	87	88	89	90	91	92	93	94	95	96	97	98	99	100	101	102	103	104	105	106	107	108	109	110	111	112	113	114	115	116	117	118	119	120	121	122	123	124	125	126	127	128	129	130	131	132	133	134	135	136	137	138	139	140	141	142	143	144	145	146	147	148	149	150	151	152	153	154	155	156	157	158	159	160	161	162	163	164	165	166	167	168	169	170	171	172	173	null	175	176	177	null	179	180	181	182	183	184	185	186	187	188	null	190	191
Price	$3	$5	$7	$9	$11	$13	null	$17	$19	$21	$23	$25	$27	$29	$31	$33	$35	$37	$39	$41	$43	$45	$47	$49	$51	$53	$55	$57	$59	$61	$63	$65	$67	$69	$71	$73	$75	$77	$79	$81	$83	$85	$87	$89	$91	$93	$95	$97	$99	$101	$103	$105	$107	$109	$111	$113	$115	$117	$119	$121	$123	$125	$127	$129	$131	$133	$135	$137	$139	$141	$143	$145	$147	$149	$151	$153	$155	$157	$159	$161	$163	$165	$167	$169	$171	$173	$175	$177	$179	$181	$183	$185	$187	$189	$191	$193	$195	$197	$199	$201	$203	$205	$207	$209	$211	$213	$215	$217	$219	$221	$223	$225	$227	$229	$231	$233	$235	$237	$239	$241	$243	$245	$247	$249	$251	$253	$255	$257	$259	$261	$263	$265	$267	$269	$271	$273	$275	$277	$279	$281	$283	$285	$287	$289	$291	$293	$295	$297	$299	null	$303	$305	$307	null	$311	$313	$315	$317	$319	$321	$323	$325	$327	$329	null	$333	$335

Thank you,

Re: Delete Columns with certain characters

Reeza — Wed, 14 Oct 2020 19:24:02 GMT

This data structure violates the majority of guidelines on how to store data.
Is it possible to first restructure your data (PROC TRANSPOSE) to a long format? If so, I'd do that first and then use the solution below.
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

Are you familiar with the concept of tidy data and why it's important or would you like a reference?

If your data was tidy the solution would be a single line of code:

if whichc('null', date, amount, price) > 0 then delete;

Re: Delete Columns with certain characters

michokwu — Wed, 14 Oct 2020 20:44:57 GMT

This is not my data set, just a random sample set. Mine is well structured but have columns with null values. I 'd like to drop those variables.

Thank you.

Re: Delete Columns with certain characters

Reeza — Wed, 14 Oct 2020 20:56:14 GMT

If your data is structured well, not as shown or as in the Excel file, then the code I included should answer your question.

If this is a one time process, fix it in Excel.
You can highlight the table, Copy>Paste Special>Transpose

Import that data set and then the code I showed will definitely work. Ultimately your choice though.
Here's cute recent thread on tidy data
https://twitter.com/juliesquid/status/1315710359404113920?s=21

Re: Delete Columns with certain characters

CurtisMackWSIPP — Wed, 14 Oct 2020 21:10:47 GMT

Rezzas answer is on point. However here is one way to do it;

proc import file="c:\temp\Groceries.xlsx" out = Groceries; run;

data _null_;
  set Groceries end = finished;
  length keepvar $5000;
  retain keepvar;
  array _Chars $ _character_;
  do over _Chars;
    if _Chars ne "null" then do;
      if not findw(keepvar,vname(_Chars)) and vname(_Chars) ne "keepvar" then keepvar = catx(' ',keepvar,vname(_Chars));
    end;
  end;
  if finished then do;
    call symput("keepvar",keepvar);
  end;
run;
%put &keepvar;
data Groceries2;
  set Groceries(keep = &keepvar);
run;

Re: Delete Columns with certain characters

michokwu — Fri, 16 Oct 2020 20:46:39 GMT

@CurtisMackWSIPP thanks, this works but keepvar does not include all the variables with 'null value

Re: Delete Columns with certain characters

CurtisMackWSIPP — Mon, 19 Oct 2020 14:12:05 GMT

Correct, it only has the ones that DON'T contain 'null'. Hence the name KEEP.

Re: Delete Columns with certain characters

michokwu — Mon, 19 Oct 2020 15:02:06 GMT

No. It contains both. See my screenshot

Re: Delete Columns with certain characters

Reeza — Mon, 19 Oct 2020 15:38:52 GMT

The most 'efficient' solution with to run a proc freq on a column and ones with NLEVELS = 1 and all null should then be deleted if you don't want to do the transpose. If you plan to every summarize any of the numeric values this format will not work for that.

Re: Delete Columns with certain characters

CurtisMackWSIPP — Mon, 19 Oct 2020 17:22:51 GMT

I think you are looking at the Groceries dataset. The code is creating a new dataset named Groceries2 with the correct fields. If you want to overwrite the original, just change Groceries2 to Groceries.