proc Sql

[harrylui]

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

[ChrisNZ · Accepted solution]

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]

thanks fro your help

[enatx]

---

@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 ?