- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I want to make a variable flag,if the max(date) of three tables— A,B,C are same then let flag=1 else let flag=0
proc sql noprint;
select max(date) into:date01 from tableA;
select max(date) into:date02 from tableB;
select max(date) into:date03 from tableC;
quit;
%put &date01@@&date02@@&date03;
I got the result:
28FEB2018@@28FEB2018@@31JAN2018
and I am using codes below to set vaule for variable flag,it returns error 72-185 for year and month function,and error22-322 for syntax wrong.why and how should I fix?
data _null_;
%global flag;
if year(&date01)=year(&date02)=year(&date03)=year(today())
and month(&date01)=month(&date02)=month(&date03)=month(today())
then
do;
%let flag=1;
end;
else
do;
%let flag=0;
end;
run;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Geo- wrote:
maybe I am not clear enough,I test your code and get a wrong answer..still thank you for your help and advice..
Your initial requirement was: "I want to make a variable flag,if the max(date) of three tables— A,B,C are same then let flag=1 else let flag=0"
The code I've posted using the sample data I've posted does exactly that. If the sample data is not representative for your real case then you should post sample data (data steps creating such data), describe the logic you need and then show us the desired result.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
and I am using codes below to set vaule for variable flag,it returns error 72-185 for year and month function,and error22-322 for syntax wrong.why and how should I fix?
It's wrong because it's invalid syntax:
Assuming literal find/replace:
This:
if year(&date01)=year(&date02)=year(&date03)=year(today())
Becomes:
if year(28FEB2018)=year(28FEB2018)=year(31JAN2018)=year(today())
This is invalid because you specify dates in SAS as a date literal: "date"d
Using something like below will work instead:
if year("&date01"d)
@Geo- wrote:
I want to make a variable flag,if the max(date) of three tables— A,B,C are same then let flag=1 else let flag=0
proc sql noprint;
select max(date) into:date01 from tableA;
select max(date) into:date02 from tableB;
select max(date) into:date03 from tableC;
quit;
%put &date01@@&date02@@&date03;
I got the result:
28FEB2018@@28FEB2018@@31JAN2018
and I am using codes below to set vaule for variable flag,it returns error 72-185 for year and month function,and error22-322 for syntax wrong.why and how should I fix?
data _null_;%global flag;
if year(&date01)=year(&date02)=year(&date03)=year(today())
and month(&date01)=month(&date02)=month(&date03)=month(today())
then
do;
%let flag=1;
end;
else
do;
%let flag=0;
end;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
return :year(today())
how to deal with this?to extract the year of today..
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Even if you fix the syntax as @Reeza suggested, your program will not work. A DATA step is not able to execute a %LET statement. Where your program reads:
%let flag = 1;
You can get the DATA step to create a macro variable named FLAG by switching to:
call symput('flag', '1');
Of course, the same applies to assigning FLAG a value of 0.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
and using %put year(today());
return :year(today())
how to deal with this?to extract the year of today..
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Do you want all three dates to be equal, or do you want just the year and month to be equal regardless of the day?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
year(&date01)=year(&date02)=year(&date03)=year(today())
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
None of that requires a DATA step. It does require a macro definition to be able to use %IF %THEN:
%if %substr(&date01, 6) = %substr(&date02, 6)
and %substr(&date01, 6) = %substr(&date03, 6)
and %substr(&date01, 6) = %substr(&sysdate9, 6) %then %let flag=1;
If you actually want to compare both the year and the month, change 6 to 3 within all the %SUBSTR functions.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Within a DATA step:
if substr("&date01", 6) = substr("&date02", 6) = substr("&date03", 6) = substr("&sysdate9", 6) then call symput('flag', '1');
Don't make it harder than it has to be.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It appears that you only want to compare year and month but not day. In order to do so just align your dates to the beginning of the month (using the intnx() function) as then you can just compare exact dates.
Below a code sample for how this could work.
data tblA tblB tblC;
dt=date(); output;
dt=dt-1; output;
stop;
run;
%let flg=;
proc sql noprint;
select
case
when a.mdt=b.mdt and a.mdt=c.mdt then '1'
else '0'
end
into :flg
from
(select intnx('month',max(dt),0,'b') as mdt from tblA) as a,
(select intnx('month',max(dt),0,'b') as mdt from tblB) as b,
(select intnx('month',max(dt),0,'b') as mdt from tblC) as c
;
quit;
%put &=flg;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content