Select all with condition

[tinton]

PROC SQL; CREATE TABLE V1 AS SELECT * FROM ACTUAL WHERE SUBSTR(MFCUST14,-3,3) IN ('HCI','CIW','HFI','FIW);

Why there's empty output eventhough MFCUST14=M9005FIW is in the table?

[tinton]

It's okay, I've figure it out. Just use Select * from actual where mfcust14 like '%HFI';

[SAS76FR · Accepted solution]

I think  SUBSTR(MFCUST14,-3,3) is empty 

try this 

PROC SQL; CREATE TABLE V1 AS SELECT *  FROM ACTUAL WHERE SUBSTR(MFCUST14,length(MFCUST14)-2,3) IN ('HCI','CIW','HFI','FIW');quit;

[MayurJadhav]

I believe you want to read last three characters from the variable MFCUST14 and compare it with ('HCI','CIW','HFI','FIW). If this is true then you need to calculate length of the string first and then subtract -2 from it. The strip function used here to remove all leading or trailing blanks from a variable MFCUST14.

The below code will give you better understanding:

data _null_;
	MFCUST14='M9005FIW';
	sub_str=SUBSTR(strip(MFCUST14), length(MFCUST14)-2, 3);
	put MFCUST14=;
	put sub_str=;
run;

Log:

69 data _null_;

70 MFCUST14='M9005FIW';

71 sub_str=SUBSTR(strip(MFCUST14), length(MFCUST14)-2, 3);

72 put MFCUST14=;

73 put sub_str=;

74 run;

MFCUST14=M9005FIW

sub_str=FIW

If this is your objective then you need to adjust your proc sql code accordingly. But I'd suggest using data step instead of a proc sql.

data actual;
	MFCUST14='M9005FIW';
	output;
	MFCUST14='M9005FI3';
	output;
	MFCUST14='M9CIW';
	output;
	MFCUST14='M9005XYZ';
	output;
run;

data V1;
	set ACTUAL;

	if SUBSTR(MFCUST14, length(MFCUST14)-2, 3) IN ('HCI', 'CIW', 'HFI', 'FIW') then
		output;
run;