Hello mentors,
Requesting some guidance.
I have a dataset ’abc’ with dates which looks like this:
DT |
23Nov2019 |
27Nov2019 |
27Nov2019 |
27Nov2019 |
27Nov2019 |
08Nov2019 |
28Nov2019 |
Where DT is num date9.
I have a macro variable ‘peeps’ such that the value of peeps when resolved is
DT = ‘2019-11-28’ or DT = ‘2019-11-23’
What I’m trying to do is if any of the dates in my dataset match up with the date values in the peeps macro variable, then flag=1. The value of peeps cannot be altered since it’s being used by other folks too.
Desired output:
DT | flag |
23Nov2019 | 1 |
27Nov2019 |
|
27Nov2019 |
|
27Nov2019 |
|
27Nov2019 |
|
08Nov2019 |
|
28Nov2019 | 1 |
I tried the following approaches to solve for this:
Trial 1:
Data abc;
Set abc;
If (&peeps.) then flag=1;
Run;
This gave me this output:
DT | flag |
23Nov2019 |
|
27Nov2019 |
|
27Nov2019 |
|
27Nov2019 |
|
27Nov2019 |
|
08Nov2019 |
|
28Nov2019 |
|
Trial 2:
Data abc;
Set abc;
Format DT yymmddd10.;
Run;
Which changed my dataset to this:
DT |
2019-11-23 |
2019-11-27 |
2019-11-27 |
2019-11-27 |
2019-11-27 |
2019-11-08 |
2019-11-28 |
Then when I tried this, I get the same output as above:
Data abc;
Set abc;
If (&peeps.) then flag=1;
Run;
Result:
DT | Flag |
23Nov2019 |
|
27Nov2019 |
|
27Nov2019 |
|
27Nov2019 |
|
27Nov2019 |
|
08Nov2019 |
|
28Nov2019 |
|
Trial 3:
data abc;
set abc;
DT2=cat("'" , put(DT,yymmddd10.),"'" );
run;
data abc;
set abc(rename=(DT=MAIN_DT DT2= DT));
run;
which converted abc dataset to:
MAIN_DT | DT |
23Nov2019 | '2019-11-23' |
27Nov2019 | '2019-11-27' |
27Nov2019 | '2019-11-27' |
27Nov2019 | '2019-11-27' |
27Nov2019 | '2019-11-27' |
08Nov2019 | '2019-11-08' |
28Nov2019 | '2019-11-28' |
Followed by:
Data abc;
Set abc;
If (&peeps.) then flag=1;
Run;
Same empty flag Result:
DT | DT |
| flag |
23Nov2019 | '2019-11-23' |
|
|
27Nov2019 | '2019-11-27' |
|
|
27Nov2019 | '2019-11-27' |
|
|
27Nov2019 | '2019-11-27' |
|
|
27Nov2019 | '2019-11-27' |
|
|
08Nov2019 | '2019-11-08' |
|
|
28Nov2019 | '2019-11-28' |
|
|
I also tried replacing 'peeps' by the actual macro variable value with all the above 3 trial methods, but still not able to get the desired result.
Data abc;
Set abc;
If DT = ‘2019-11-28’ or DT = ‘2019-11-23’
then flag=1;
Run;
Appreciate the help, thank you!
SAS date literals need to be written in date9 format, in quotes with an immediately following letter d, like
"23nov2019"d
Dates that come in other form need to be converted by using the input() function with a proper informat, in your case yymmdd10.
It is interesting to know from where you derive your macro variable, as this could point the way to a solution not involving unwieldy code snips in macro variables.
Assuming you have DT is a SAS date:
Data abc;
Set abc;
If DT = '28Nov2019'd or DT = '23Nov2019'd
then flag=1;
Run;
1) Your date DT is a sas numeric variable and contains the number of days since 01JAN1960 (= 0 days);
The format does not change the value, it is used for display/print only.
2) Date literal is in format of 'ddmmmyy'd which means you need change the macro PEEPS:
%let peeps = '23nov2019'd '28nov2019'd;
/* to be used as: */
if DT in (&peeps) then flag=1;
/* or */
%let peeps = dt='23nov2019'd or dt='28nov2019'd;
/* to be used as: */
if &peeps then flag=1;
Otherwise you need to replace the original macro peeps by a program:
%let pees = DT = ‘2019-11-28’ or DT = ‘2019-11-23’;
data _NULL_:
original = "&peeps";
date1 = input(scan(original,2." '= ");
date2 = input(scan(original,5," ,= ");
peeps = "dt="!!put(date1,date9.)!!"'d or dt="!!put(date2,date9.)!!"'d" ;
call symput('peeps',peeps);
run;
/*to be used as */
if &peeps then flag=1;
Why did you add quotes around the value when you converted the date values into string values? A value with quotes in it will not match the value you were trying to test for. "‘2019-11-28’" is not equal to ‘2019-11-28’ .
data want ;
set abc (rename=(DT=date));
dt=put(date,yymmdd10.);
if &peeps then flag=1;
run;
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 25. 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.