OK, I think I might have something for you. Below is some SAS code. Below the code are the results. This code will parse out the values you're looking for no matter the word order and no matter placement. In other words if the type of car (automatic vs. manual) comes before the model of the car (Prius, Camry, etc.), it would still be picked up.
This code requires that you define a table of the cars (or whatever it really is) that you're looking for as well as the possible types and then does a double array search of each line of data. Note that if a definition is omitted, e.g. the Volt, "unknown" will be returned.
IMPORTANT: This code does not allow embedded blanks. If you wanted, for example "Accord LX", you would need to code "Accord_LX" (or "Accord-LX") in your both data and in your table definitions.
See what you think.
Jim
DATA Have;
INFILE DATALINES TRUNCOVER;
INPUT
Data_To_Examine $CHAR128.;
;
DATALINES;
Camry red Manual
Corolla Blue Automatic
Yugo (Yugoslavia) Manual
Tiguan Germany Automatic
Camry Blue (Japan) - Manual
Prius (America) Toyota Automatic
Volt (US) Automatic
Blue Automatic Honda Civic 4-Door
Gray Manual Accord LX 2012
;
RUN;
DATA Cars_Table;
INFILE DATALINES TRUNCOVER;
INPUT
Car $CHAR32.;
;
DATALINES;
Camry
Corolla
Prius
Accord
Civic
;
RUN;
PROC SQL NOPRINT;
SELECT COUNT(Car)
INTO : Car_Count TRIMMED
FROM Cars_Table;
QUIT;
%PUT NOTE: &=Car_Count;
PROC TRANSPOSE DATA=Cars_Table
OUT =Cars_Array (DROP=_:)
Prefix=Car_
;
Var Car;
RUN;
DATA Types_Table;
INFILE DATALINES TRUNCOVER;
INPUT
Type $CHAR32.;
;
DATALINES;
Automatic
Manual
;
RUN;
PROC SQL NOPRINT;
SELECT COUNT(Type)
INTO : Type_Count TRIMMED
FROM Types_Table;
QUIT;
%PUT NOTE: &=Type_Count;
PROC TRANSPOSE DATA=Types_Table
OUT =Types_Array (DROP=_:)
Prefix=Type_
;
Var Type;
RUN;
DATA Want;
DROP _:;
DROP Car_1 - Car_&Car_Count;
DROP Type_1 - Type_&Type_Count;
IF _N_ = 1 THEN
DO;
SET Cars_Array;
SET Types_Array;
END;
ARRAY Cars {&Car_Count} $32 Car_1 - Car_&Car_Count;
ARRAY Types {&Type_Count} $32 Type_1 - Type_&Type_Count;
SET Have;
LENGTH Car_Var $32;
LENGTH Type_Var $32;
Car_Var = 'Unknown';
Type_Var = 'Unknown';
DO _i = 1 TO &Car_Count;
IF INDEX(UPCASE(Data_To_Examine), UPCASE(STRIP(Cars{_i}))) THEN
DO;
Car_Var = Cars{_i};
DO _j = 1 TO &Type_Count;
IF INDEX(UPCASE(Data_To_Examine), UPCASE(STRIP(Types{_j}))) THEN
DO;
Type_Var = Types{_j};
END;
END;
END;
END;
RUN;Results:
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.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
2731 views
-
2 likes
-
-
