I am trying to create a subset of a table that I have already created. I create the table as one row for each farmer, and 3 columns for each vegetable selected. However, the number of vegetables selected changes with each run, shown in the vegetables and vegetables_expanded tables below. I want to be able to dynamically subset either table (or a larger table) to only get the final want table. I want the final want table to be able to populate based on any vegetable. (i.e., keep farmer info and carrot fields, OR keep farmer info and rutabaga fields). However, I do NOT want to explicitly specify which farmer fields (Farmer, Address, State) to keep. data vegetables;
infile datalines delimiter=',';
length Farmer $15 Address $15 carrot_location $20 beet_location $20;
input Farmer $ State $ Address $ carrot_number carrot_location $ carrot_price beet_number beet_location $ beet_price;
datalines;
Old McDonald, MC, Farm 1, 12, Field 1, 3.21, 14, Field 3, .12
Dwight Schrute, PA, Rural Rt 6, 546, Slaughterhouse Field, 1.06, 12956, Winery Field, .27
Luke Skywalker, TA, Chottt el Jerid 1138, 57, Salt Flat 2, .54, 23, Salt Flat 3, .23
;
data vegetables_expanded;
infile datalines delimiter=',';
length Farmer $15 Address $15 carrot_location $20 beet_location $20 rutabega_location $11;
input Farmer $ State $ Address $ carrot_number carrot_location $ carrot_price beet_number beet_location $ beet_price rutabega_number rutabega_location $ rutabega_price;
datalines;
Old McDonald, MC, Farm 1, 12, Field 1, 3.21, 14, Field 3, .12, 245, Field 2, 4.31
Dwight Schrute, PA, Rural Rt 6, 546, Slaughterhouse Field, 1.06, 12956, Winery Field, .27, 0, N/A, 0
Luke Skywalker, TA, Chottt el Jerid 1138, 57, Salt Flat 2, .54, 23, Salt Flat 3, .23, 43, Salt Flat 2, .12
;
data want1;
infile datalines delimiter=',';
length Farmer $15 Address $15 carrot_location $20;
input Farmer $ State $ Address $ carrot_number carrot_location $ carrot_price;
datalines;
Old McDonald, MC, Farm 1, 12, Field 1, 3.21
Dwight Schrute, PA, Rural Rt 6, 546, Slaughterhouse Field, 1.06
Luke Skywalker, TA, Chottt el Jerid 1138, 57, Salt Flat 2, .54
; I have been trying to find a solution using the sashelp vcolumn table. However, my solution requires me to know what I do NOT want to include. I'm having a hard time coming up with a dynamic solution because the number of filters I need changes depending on the number of vegetables I include in the original table. Does anyone have any suggestions on how to code this dynamically? I have a piece of code that utilizes the like functionality to exclude columns that I do not want. This works statically, but I can't quite get it to work dynamically. Here is my attempt: *Disclaimer - I stole the dynamic code from a previous solution from @Kurt_Bremser, which can be found here *Works - static;
data columns;
set sashelp.vcolumn;
where memname = UPCASE("vegetables_expanded") and name not like upcase("beet%") and name not like upcase("rutabega%")
and name not like lowcase("beet%") and name not like lowcase("rutabega%") and name not like lowcase("%beet") and name not like lowcase("%rutabega")
and name not like upcase("%beet") and name not like upcase("%rutabega");
run;
*Doesn't work - dynamic;
data _null_;
call execute('data');
do until (eof1);
set veggies (where=(veggie = "carrot")) end=eof1;
call execute(" columns_" !! veggie);
end;
call execute('; set sashelp.vcolumn;');
do until (eof2);
set veggies (where=(veggie ne "carrot")) end=eof2;
call execute('where memname = UPCASE("vegetables_expanded") and name not like upcase("' !! strip(veggie) !!'%") and name not like lowcase("' !! strip(veggie) !! '%")
and name not like upcase("%' !! strip(veggie) !! '") and name not like lowcase("%' !! strip(veggie) !! '");');
end;
call execute('run;');
stop;
run;
... View more