topic proc Sql in SAS Programming

proc Sql

harrylui — Fri, 03 Apr 2020 02:45:15 GMT

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

Re: proc Sql

ChrisNZ — Fri, 03 Apr 2020 10:18:35 GMT

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.

Re: proc Sql

enatx — Sat, 04 Apr 2020 04:04:22 GMT

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

Re: proc Sql

harrylui — Wed, 08 Apr 2020 08:26:53 GMT

thanks fro your help