Create a macro varaible with condional on exisiting of a data set

[Ronein]

Hello

I want to do the following :

There is a data set with purchase transactions called trans_tbl with following columns:ID,Date,amount.

Please note that date column is type of  sas date value.

I want to create a macro varaible called Mon that :

1- If data set trans_tbl doesn't exist then It will get value 2009

2- If data set trans_tbl  exist then It will get value of  put(Min(date),YYMMn4.)

What is the way to do it please?

Data trans_tbl ;
Format date date9.;
Input Id date : date9. amount;
cards;
1 18FEB2021 100
2 21FEB2021 200
3 08FEB2021 300
4 11FEB2021 100
5 27FEB2021 400
6 02FEB2021 800
7 20FEB2021 900
8 29FEB2021 100
9 17FEB2021 200
10 15FEB2021 300
;
Run;

/**
If trans_tbl  exists then  %let Mon=2009;
else then 
proc sql noprint;
   select  put(min(date),yymmn4.)
      into :Mon
      from trans_tbl;
 quit;
%put &Mon.
**/

[PaigeMiller]

You can use the method in your earlier thread to determine what exists and what doesn't exist:

https://communities.sas.com/t5/SAS-Programming/IMPORT-IF-EXISTS-AND-CREATE-EMPTY-DATASET-IF-NOT/m-p/...

You can use the EXIST() function for SAS data sets instead of the FILEEXIST function which works for operating system files.

[Ronein]

data Null ;
if EXIST(trans_tbl ) then do;
proc sql noprint;
   select  put(min(date),yymmn4.)
      into :Mon
      from trans_tbl;
quit;
End;
Else do;
%let Mon=2009;
End;
Run;

Thanks,Will it work well?If not,may you correct the code and explain 

[PaigeMiller]

if EXIST(trans_tbl ) then do;

This has to be done with macro statements

%if %sysfunc(exist(trans_tbl)) %then %do;

and of course the rest of the code has to be adjusted as well (you need %END; and %ELSE %DO and so on)

[Ronein]

Thanks!

Should I put all in a macro or is it fine to wrote it in Data set null?

Do you think this code will work well?

Data trans_tbl ;
Format date date9.;
Input Id date : date9. amount;
cards;
1 18FEB2021 100
2 21FEB2021 200
3 08FEB2021 300
4 11FEB2021 100
5 27FEB2021 400
6 02FEB2021 800
7 20FEB2021 900
8 29FEB2021 100
9 17FEB2021 200
10 15FEB2021 300
;
Run;


data Null ;
%if %sysfunc(exist(trans_tbl)) %then %do;
proc sql noprint;
   select  put(min(date),yymmn4.)
      into :Mon
      from trans_tbl;
quit;
%End;
%Else do;
%let Mon=2009;
%End;
Run;

[Cynthia_sas]

Did you try running the code to see whether it worked?
Cynthia

[Ronein]

No yet because I  have no SAS in my home PC but the most important is to understand the cocept and the correct code

[PaigeMiller]

You can't put a PROC SQL inside a DATA NULL. You can put a PROC SQL inside a MACRO or inside a macro %IF/%THEN/%ELSE.

It's not even clear why you would want a DATA NULL here. (And technically, it's DATA _NULL_, if you leave off the underscores it has a different meaning). As shown in your earlier thread, no DATA NULL or DATA _NULL_ is used.

[Ronein]

Thanks a lot!

So as I understand this is the correct code .

Is there anything else to correct?

Data trans_tbl ;
Format date date9.;
Input Id date : date9. amount;
cards;
1 18FEB2021 100
2 21FEB2021 200
3 08FEB2021 300
4 11FEB2021 100
5 27FEB2021 400
6 02FEB2021 800
7 20FEB2021 900
8 29FEB2021 100
9 17FEB2021 200
10 15FEB2021 300
;
Run;

%macro exist;
data Null ;
%if %sysfunc(exist(trans_tbl)) %then %do;
proc sql noprint;
   select  put(min(date),yymmn4.)
      into :Mon
      from trans_tbl;
quit;
%End;
%Else do;
%let Mon=2009;
%End;
Run;
%mend;
%exist;

[Ronein]

Should I delete Data NULL statement? 

[PaigeMiller]

data null not needed (and the associated RUN;)

No semicolon needed after %EXIST

[Ronein]

Why data null is not needed? 

Can it run with data null or it is an error to add data null statement?

Why should I delete semicolon after "%if %sysfunc(exist(trans_tbl)) %then %do " ?

Data trans_tbl ;
Format date date9.;
Input Id date : date9. amount;
cards;
1 18FEB2021 100
2 21FEB2021 200
3 08FEB2021 300
4 11FEB2021 100
5 27FEB2021 400
6 02FEB2021 800
7 20FEB2021 900
8 29FEB2021 100
9 17FEB2021 200
10 15FEB2021 300
;
Run;

%macro exist;
%if %sysfunc(exist(trans_tbl)) %then %do 
/**I removed semicolon here**/
proc sql noprint;
   select  put(min(date),yymmn4.)
      into :Mon
      from trans_tbl;
quit;
%End;
%Else %do;
%let Mon=2009;
%End;
%mend;
%exist;

[PaigeMiller]

---

@Ronein wrote:

Why data null is not needed? 

Can it run with data null or it is an error to add data null statement?

---

Let me ask you the question: why do you think DATA NULL is needed? What data step functionality is being used here?

Why should I delete semicolon after "%if %sysfunc(exist(trans_tbl)) %then %do " ?

Please read carefully. No such statement was made.

[Ronein]

May you please show the full code in order to see where should i delete the semi colone as you mentioned before?
Thank you so much

[PaigeMiller]

This is what I said:

No semicolon needed after %EXIST

where is %EXIST in your code?