topic Re: Create a macro varaible with condional on exisiting of a data set in SAS Programming

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

Ronein — Sun, 18 Jul 2021 12:31:04 GMT

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.
**/

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

PaigeMiller — Sun, 18 Jul 2021 12:46:07 GMT

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/745824#M233877

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

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

Ronein — Sun, 18 Jul 2021 12:49:58 GMT

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

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

PaigeMiller — Sun, 18 Jul 2021 12:52:21 GMT

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)

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

Ronein — Sun, 18 Jul 2021 13:00:32 GMT

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;

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

Cynthia_sas — Sun, 18 Jul 2021 13:03:48 GMT

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

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

Ronein — Sun, 18 Jul 2021 13:04:47 GMT

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

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

PaigeMiller — Sun, 18 Jul 2021 13:07:48 GMT

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.

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

Ronein — Sun, 18 Jul 2021 13:09:25 GMT

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;

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

Ronein — Sun, 18 Jul 2021 13:10:33 GMT

Should I delete Data NULL statement?

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

PaigeMiller — Sun, 18 Jul 2021 13:11:58 GMT

data null not needed (and the associated RUN;)

No semicolon needed after %EXIST

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

Ronein — Mon, 19 Jul 2021 04:49:29 GMT

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;

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

Tom — Sun, 18 Jul 2021 13:21:49 GMT

If you are using a reasonably recent release of SAS you don't need do define the macro. Simple %IF/%THEN/%DO/%END/%ELSE/%DO blocks like that will work in open code.

%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;

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

PaigeMiller — Sun, 18 Jul 2021 13:56:19 GMT

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

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

Ronein — Sun, 18 Jul 2021 14:03:45 GMT

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

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

PaigeMiller — Sun, 18 Jul 2021 14:07:01 GMT

This is what I said:

No semicolon needed after %EXIST

where is %EXIST in your code?

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

Ronein — Sun, 18 Jul 2021 14:38:57 GMT

Should i use %exist or exist??
Can you explain please .

May you show the full code of your solution?

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

PaigeMiller — Sun, 18 Jul 2021 14:54:18 GMT

EXIST is a function provided by SAS, that you determines if a data set exists. You need to use it.

%EXIST is a macro that you wrote, and does other things.

The proper call to the macro, in the last line of your program is:

%EXIST

and not

%EXIST;

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

Tom — Sun, 18 Jul 2021 15:12:38 GMT

SAS uses semi-colon to mark the end of a statement (both regular SAS statements and macro processor statements). So in some cases having an extra semi-colon can change the meaning of the code by terminating a statement too early.

But note that this is not really an important distinction in this case. If the dataset exists then having the extra semi-colon after the macro call means that instead of running this code:

proc sql noprint;
   select  put(min(date),yymmn4.)
      into :Mon
      from trans_tbl;
quit;

You will have run this code instead:

proc sql noprint;
   select  put(min(date),yymmn4.)
      into :Mon
      from trans_tbl;
quit;;

SAS will see that extra semi-colon as an empty statement and so not do anything different that it would without it.

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

PaigeMiller — Sun, 18 Jul 2021 15:13:27 GMT

@Tom

True, but it can be an important distinction in other cases, and in my opinion, putting a semicolon after the call to a macro is a bad habit to get into.