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.
**/
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;
You can use the method in your earlier thread to determine what exists and what doesn't exist:
You can use the EXIST() function for SAS data sets instead of the FILEEXIST function which works for operating system files.
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
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)
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;
No yet because I have no SAS in my home PC but the most important is to understand the cocept and the correct code
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.
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;
Should I delete Data NULL statement?
data null not needed (and the associated RUN;)
No semicolon needed after %EXIST
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;
@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.
This is what I said:
No semicolon needed after %EXIST
where is %EXIST in your code?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.