topic Re: Select all with condition in New SAS User

Select all with condition

tinton — Thu, 09 Feb 2023 10:01:27 GMT

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?

Re: Select all with condition

tinton — Thu, 09 Feb 2023 10:22:56 GMT

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

Re: Select all with condition

SAS76FR — Thu, 09 Feb 2023 10:44:07 GMT

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;

Re: Select all with condition

MayurJadhav — Thu, 09 Feb 2023 10:44:25 GMT

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;