BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Geo-
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

@Geo-

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.

View solution in original post

15 REPLIES 15
Reeza
Super User

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"

 

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;


 

Geo-
Quartz | Level 8
thanks..%put year(today());
return :year(today())
how to deal with this?to extract the year of today..
Kurt_Bremser
Super User

@Geo- wrote:
thanks..%put year(today());
return :year(today())
how to deal with this?to extract the year of today..

To use data step functions in macro statements, you need the %sysfunc macro function.

Astounding
PROC Star

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.

Geo-
Quartz | Level 8
thank you for your advice,would you please write the exact codes..much appreciated.

and using %put year(today());
return :year(today())
how to deal with this?to extract the year of today..
Astounding
PROC Star

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?

Geo-
Quartz | Level 8
I want three dates' year and today's year equal,like:
year(&date01)=year(&date02)=year(&date03)=year(today())
Astounding
PROC Star

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.

Geo-
Quartz | Level 8
would you please use my code to finish a syntax right version..?
Astounding
PROC Star

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.

Patrick
Opal | Level 21

@Geo-

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;
Geo-
Quartz | Level 8
would you please use my code to finish a syntax right version..?
Patrick
Opal | Level 21

@Geo- wrote:
would you please use my code to finish a syntax right version..?

 

@Geo-

The code I've posted implements the logic you're after based on your initial post.

If you're looking for a developer working for you then start offering payment!

Geo-
Quartz | Level 8
maybe I am not clear enough,I test your code and get a wrong answer..still thank you for your help and advice..

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 1431 views
  • 0 likes
  • 5 in conversation