good day,
here is the code i wrote. the program logic is to extract the data i needed and put it in ned data set with a new assigned name.
any skill can enhance this script? can i just simply mark the new_name in old data set?
instead of draw them out and append to a new data base.
i just find out i create many data sets and waste tons of time on waiting.
%macro sqlmerchant(A=,B=,C=,D=);
PROC SQL;
CREATE TABLE &A AS
SELECT * ,"&b" AS NAME ,"Y" as Cleaned_flag FROM TESTING
WHERE NEW2 LIKE &C
AND "Merchant Category"N IN &D
;
DELETE FROM TESTING
WHERE NEW2 LIKE &C
AND "Merchant Category"N IN &D
;
create table DATA_&A as
select * from &A
;
quit;
proc append base=testing2
data=DATA_&A ;
run;
%MEND;
%sqlmerchant(A=AIRSTANA,b=AIR STANA,C="%AIRASTANA%",D=("Airlines----AIR STANA"));
%sqlmerchant(A=AIRSTANA,b=AIR STANA,C="%AIRSTANA%",D=("Airlines----AIR STANA"));
thanks for the helping,
Harry
This should work much faster.
%macro sqlmerchant(name=,test=);
if &test then do;
Cleaned_flag="Y";
NAME="name";
end;
%mend;
data TESTING;
modify TESTING;
%sqlmerchant(name=AIRSTANA, test=index(NEW2,"AIRSTANA") & "MerchantCategory"n in ("Airlines----AIR STANA") );
%sqlmerchant(name=AIRSTANA, test=index(NEW2,"AIRASTANA") & "MerchantCategory"n in ("Airlines----AIR STANA") );
run;
Note that MODIFY does not allow creating columns.
If that's an issue, use SET instead.
This should work much faster.
%macro sqlmerchant(name=,test=);
if &test then do;
Cleaned_flag="Y";
NAME="name";
end;
%mend;
data TESTING;
modify TESTING;
%sqlmerchant(name=AIRSTANA, test=index(NEW2,"AIRSTANA") & "MerchantCategory"n in ("Airlines----AIR STANA") );
%sqlmerchant(name=AIRSTANA, test=index(NEW2,"AIRASTANA") & "MerchantCategory"n in ("Airlines----AIR STANA") );
run;
Note that MODIFY does not allow creating columns.
If that's an issue, use SET instead.
@harrylui wrote:good day,
here is the code i wrote. the program logic is to extract the data i needed and put it in ned data set with a new assigned name.
any skill can enhance this script? can i just simply mark the new_name in old data set?
instead of draw them out and append to a new data base about.
i just find out i create many data sets and waste tons of time on waiting.
%macro sqlmerchant(A=,B=,C=,D=);
PROC SQL;
CREATE TABLE &A AS
SELECT * ,"&b" AS NAME ,"Y" as Cleaned_flag FROM TESTING
WHERE NEW2 LIKE &C
AND "Merchant Category"N IN &D
;
DELETE FROM TESTING
WHERE NEW2 LIKE &C
AND "Merchant Category"N IN &D
;create table DATA_&A as
select * from &A
;
quit;proc append base=testing2
data=DATA_&A ;
run;%MEND;
%sqlmerchant(A=AIRSTANA,b=AIR STANA,C="%AIRASTANA%",D=("Airlines----AIR STANA"));
%sqlmerchant(A=AIRSTANA,b=AIR STANA,C="%AIRSTANA%",D=("Airlines----AIR STANA"));
thanks for the helping,
Harry
Did you resolve this issue ?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.