BookmarkSubscribeRSS Feed
michokwu
Quartz | Level 8

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.

 

Namestropical_fruitwhole_milkpip_fruitother_vegetablesrolls_bunspot_plantscitrus_fruitbeeffrankfurterchickenbutterfruit_vegetable_juicepackaged_fruit_vegetableschocolatespecialty_barbutter_milkbottled_wateryogurtsausagebrown_breadhamburger_meatroot_vegetablesporkpastrycanned_beerberriescoffeemisc__beverageshamturkeycurd_cheesered_blush_winefrozen_potato_productsfloursugarfrozen_mealsherbssodadetergentgrapesprocessed_cheesefishsparkling_winenewspaperscurdpastapopcornfinished_productsbeveragesbottled_beerdessertdog_foodspecialty_chocolatecondensed_milkcleanerwhite_winemeatice_creamhard_cheesecream_cheeseliquorpickled_vegetablesliquor__appetizer_UHT_milkcandyonionshair_sprayphoto_filmdomestic_eggsmargarineshopping_bagssaltoilwhipped_sour_creamfrozen_vegetablessliced_cheesedish_cleanerbaking_powderspecialty_cheesesalty_snackInstant_food_productspet_carewhite_breadfemale_sanitary_productscling_film_bagssoapfrozen_chickenhouse_keeping_productsspread_cheesedecalcifierfrozen_dessertvinegarnuts_prunespotato_productsfrozen_fishhygiene_articlesartif__sweetenerlight_bulbscanned_vegetableschewing_gumcanned_fishcookwaresemi_finished_breadcat_foodbathroom_cleanerproseccoliver_loafzwiebackcanned_fruitfrozen_fruitsbrandybaby_cosmeticsspicesnapkinswafflessaucesrumchocolate_marshmallowlong_life_bakery_productbagssweet_spreadssoupsmustardspecialty_fatinstant_coffeesnack_productsorganic_sausagesoft_cheesemayonnaisedental_careroll_productskitchen_towelsflower_soil_fertilizercerealsmeat_spreadsdishesmale_cosmeticscandleswhiskytidbitscooking_chocolateseasonal_productsliqueurabrasive_cleanersyrupketchupcreamskin_carerubbing_alcoholnut_snackcocoa_drinkssoftenerorganic_productscake_barhoneyjamkitchen_utensilflower__seeds_riceteasalad_dressingspecialty_vegetablespudding_powderready_soupsmake_up_removertoilet_cleanerpreservation_products
Member_number180825522300118749413803null2193199745464736195919742421151328104976405636114286478337094289290035273841427216972978291013774162487547501199160320221202438937462623102448283564322146992184378319233795392241872904122241824389426244941226139527941547349528361311244728794651464517653429481833794186421717431363252516562215174635713950225631203206469116321099276221221800301235792340453212732467408823361780489116303512321122033186196843014533192420703324208622504323494040881899145010472262268722904392363435111356367741724734377633851326366321002181289316544335435111494525242113232476269015404495null256025302855null3783200517484201397213732064389411002913null24841529
Date21-07-20155/1/201519-09-201512/12/201514-02-201523-12-2015null14-04-201521-07-20153/9/201521-07-201530-03-20153/5/20152/9/20153/8/20158/9/201517-07-201512/6/201513-02-20158/3/201522-10-201526-10-20158/10/201511/4/201529-09-201519-07-20156/6/201521-05-20155/6/201528-07-201514-03-201522-12-201512/5/201521-09-201510/12/201530-09-20154/3/201522-07-201520-10-201517-08-201510/10/201510/8/20158/8/201527-06-201513-01-20158/2/201530-06-201517-06-201514-02-201517-01-201527-02-20159/5/20156/6/201524-09-201512/5/201513-04-201516-10-20156/1/201513-04-20157/3/201516-09-201518-04-201527-06-201529-12-201512/2/201528-12-201518-04-201521-06-201525-07-201511/3/20154/1/201511/1/201521-01-201525-10-201518-12-201518-04-201521-06-201524-05-201530-10-201522-07-201515-02-201511/12/20151/10/201511/4/201520-10-201511/8/201512/10/201529-05-201513-08-20157/11/20157/4/201531-07-20153/5/201517-08-20157/10/201529-11-20154/5/201518-04-201523-09-201514-04-201513-10-201519-04-201522-05-201517-11-201530-09-201527-06-201525-11-201510/1/201520-12-201521-05-20152/3/201519-12-20156/6/20152/6/201515-10-201511/10/201521-08-20155/3/201519-10-201516-11-20159/8/20154/11/201514-09-20153/7/201513-08-20152/12/201515-11-20152/4/201521-04-20158/10/201512/7/201517-05-201522-06-20157/2/20154/1/201519-09-201523-06-201512/12/201525-03-20155/12/20158/5/201512/12/201511/5/201521-11-20153/11/20154/12/201520-10-201526-11-20158/4/2015null19-06-201513-06-20155/8/2015null22-06-201522-09-201514-08-20146/8/20144/5/201428-04-20146/4/201410/2/201412/3/20154/7/2015null17-08-20141/3/2014
Amount 252627282930null3233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173null175176177null179180181182183184185186187188null190191
Price $3$5$7$9$11$13null$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$299null$303$305$307null$311$313$315$317$319$321$323$325$327$329null$333$335

 

Thank you,

9 REPLIES 9
Reeza
Super User
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;
michokwu
Quartz | Level 8
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.
Reeza
Super User
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


CurtisMackWSIPP
Lapis Lazuli | Level 10

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;
michokwu
Quartz | Level 8

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

CurtisMackWSIPP
Lapis Lazuli | Level 10

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

michokwu
Quartz | Level 8

No. It contains both. See my screenshot

michokwu_0-1603119700405.png

Reeza
Super User
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.
CurtisMackWSIPP
Lapis Lazuli | Level 10

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1697 views
  • 7 likes
  • 3 in conversation