topic Re: Proc SQL with condition insert in New SAS User

Proc SQL with condition insert

Ashpak — Mon, 22 Feb 2021 21:46:26 GMT

I would like use something below like this
If cntr below value >0 then I would like to use insert operation how to do this SAS

Proc SQL;
Select count(*) into :cntr from abc :
%put &cntr;
Run:
Proc SQL ;
If cntr>0 then
Do;
Insert into xyz
Select from gggg

Re: Proc SQL with condition insert

Reeza — Mon, 22 Feb 2021 21:53:24 GMT

You would use macro logic in this case.

%if  %eval(&cntr > 0) %then %do;

%put "In IF condition";

proc sql;
insert .......

Quit;

%end;

General rule, is first make sure your code works then make sure your macro works.

I usually just use a %PUT statement to be able to verify my logic as well.

Some macro references for you, if this is a new topic:

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@Ashpak wrote:
I would like use something below like this
If cntr below value >0 then I would like to use insert operation how to do this SAS

Proc SQL;
Select count(*) into :cntr from abc :
%put &cntr;
Run:
Proc SQL ;
If cntr>0 then
Do;
Insert into xyz
Select from gggg

Re: Proc SQL with condition insert

Ashpak — Mon, 22 Feb 2021 22:29:15 GMT

I tried this way.

proc sql;

select count(*) into :cntr from NVPLocal.NVP_DOD_Catch ;

%put &cntr;

select qrm_db,strategy,moddate format=Date9. into :Var1,:Var2,:Var3 from NVPLocal.NVP_DOD_Catch ;

run;

%Macro getit;

%if %eval(&cntr > 0) %then %do;

%put "In IF condition";

proc sql;

insert into NVPLocal.NVP_PRICING_HIS

select * from FASTControl].[dbo].[ALM_NVPData]

where qrm_db=&Var1

and strategy=&Var2

and moddate=&Var3;

run;

Quit;

%Mend getit;

%getit;

getting error :

here were 1 unclosed %DO statements. The macro GETIT will not be compiled.

ERROR: A dummy macro will be compiled.

Re: Proc SQL with condition insert

Reeza — Mon, 22 Feb 2021 22:44:52 GMT

here were 1 unclosed %DO statements.

Where's the %END to end your %DO? You do not need to wrap that code within a macro, that's an older requirement, using it inline like I suggested will work with newer versions of SAS.

@Ashpak wrote:

I tried this way.

proc sql;

select count(*) into :cntr from NVPLocal.NVP_DOD_Catch ;

%put &cntr;

select qrm_db,strategy,moddate format=Date9. into :Var1,:Var2,:Var3 from NVPLocal.NVP_DOD_Catch ;

run;

%Macro getit;

%if %eval(&cntr > 0) %then %do;

%put "In IF condition";

proc sql;

insert into NVPLocal.NVP_PRICING_HIS

select * from FASTControl].[dbo].[ALM_NVPData]

where qrm_db=&Var1

and strategy=&Var2

and moddate=&Var3;

run;

Quit;

%Mend getit;

%getit;

getting error :

here were 1 unclosed %DO statements. The macro GETIT will not be compiled.

ERROR: A dummy macro will be compiled.

Re: Proc SQL with condition insert

Ashpak — Mon, 22 Feb 2021 22:51:59 GMT

proc sql;
select count(*) into :cntr from NVPLocal.NVP_DOD_Catch ;
%put &cntr;

select qrm_db,strategy,moddate format=Date9. into :Var1,:Var2,:Var3 from NVPLocal.NVP_DOD_Catch ;
run;
%macro getit
%if %eval(&cntr > 0) %then %do;
%put "In IF condition";
proc sql;
insert into NVPLocal.NVP_PRICING_HIS
select * from FASTControl].[dbo].[ALM_NVPData]
where qrm_db=&Var1
and strategy=&Var2
and moddate=&Var3;
run;
%end;
%getit;

error message
ERROR: Expected semicolon not found. The macro will not be compiled.
ERROR: A dummy macro will be compiled.

Re: Proc SQL with condition insert

Ashpak — Mon, 22 Feb 2021 22:53:17 GMT

ERROR: Expected semicolon not found. The macro will not be compiled.

ERROR: A dummy macro will be compiled.

proc sql;

select count(*) into :cntr from NVPLocal.NVP_DOD_Catch ;

%put &cntr;

select qrm_db,strategy,moddate format=Date9. into :Var1,:Var2,:Var3 from NVPLocal.NVP_DOD_Catch ;

run;

%macro getit

%if %eval(&cntr > 0) %then %do;

%put "In IF condition";

proc sql;

insert into NVPLocal.NVP_PRICING_HIS

select * from FASTControl].[dbo].[ALM_NVPData]

where qrm_db=&Var1

and strategy=&Var2

and moddate=&Var3;

run;

%end;

%getit;

Re: Proc SQL with condition insert

SASKiwi — Mon, 22 Feb 2021 23:03:04 GMT

You are missing a semicolon on this statement:%macro getit

Re: Proc SQL with condition insert

Reeza — Mon, 22 Feb 2021 23:05:12 GMT

Oddly enough wasn't missing the first time....

Re: Proc SQL with condition insert

Ashpak — Mon, 22 Feb 2021 23:47:37 GMT

yes my bad missed out, Thanks for your help

Re: Proc SQL with condition insert

Ashpak — Tue, 23 Feb 2021 23:50:19 GMT

This macro is failing while executing getting error message

ERROR: Expression using greater than or equal (>=) has components that are of different data types.

data _null_;

call symputx('today',quote(put(today(),yymmdd10.),"'"));

run;

proc sql;

create table NVPLocal.tmp as select distinct qrm_db,strategy,moddate

from NVPLocal.NVP_DOD_Catch;

select count(*) into :cntr1 from NVPLocal.tmp;

quit;

%macro getit;

%if &cntr1 > 0 %then %do ;

proc sql;

insert into NVPLocal.NVP_PRICING_HIS

select &today, * from NVPLocal.temp

where qrm_db=&Var1

and strategy=&Var2

and moddate>=&Var3;

run;

%end;

%mend;

%getit;

Re: Proc SQL with condition insert

Reeza — Wed, 24 Feb 2021 15:20:59 GMT

Show the equivalent code that works without macros.
Since the error indicates a mistmatch of data types around an equality sign it's likely an issue with your WHERE condition - do you need quotes around one of the variables?

Re: Proc SQL with condition insert

Ashpak — Fri, 26 Feb 2021 23:48:02 GMT

Thanks yeah it was issue with Where condition I corrected and its working now thanks for your help