Scan a list of datetime16.

[linlin87]

Hi Everybody,

I have a list of datetimes that I have made via SQL:

proc sql;
select distinct sample_dtm into :vardtm separated by ","
from sampleID_database
run;
%put &vardtm;

And the put statement gives me a list of three datetimes as required:

02FEB20:15:20:00,12JUN20:15:26:50,24JUL20:16:20:12

Now I want to do some operations with these dates. For a dataset, I want to (for example) say that if the date of an event is before the second instance of &vardtm but after the first instance of &vardtm, then do such-and-such.

So I guessed I would have to use the SCAN function to get these different parts of my macro list. I thought that the following would identify the first datetime in my list, but it does not:

%put %scan(&vardtm,1,',');

Instead, it gives the error:

ERROR: A character operand was found in the %EVAL function or %IF
condition where a numeric operand is required. The
condition was: 02FEB20:15:20:00
ERROR: Argument 2 to macro function %SCAN is not a number.

And what's more, if I do for example the following to actually do what I hoped to do, it obviously does not work:

data want;
	set have;
	sampleid=.;
	if %scan(&vardtm,2,',') le dtm le %scan(&vardtm,2,',') then sampleid='1438';
	if dtm>%scan(&vardtm,2,',') then sampleid='2348';
run;

Any help would be really appreciated!

[Tom]

The SAS code your macro code generates needs to be valid SAS syntax.

You are currently generating this code:

if 02FEB20:15:20:00 le dtm ...

which is NOT valid.

You can either add quotes and the letters dt so that the generated code is valid.

	if "%scan(&vardtm,2,',')"dt le dtm le "%scan(&vardtm,2,',')"dt then sampleid='1438';
	if dtm>"%scan(&vardtm,2,',')"dt then sampleid='2348';

Or change the format used to create the macro variable so that the raw number of seconds is generated instead of the text that humans can look at.

select distinct sample_dtm format=best32.
  into :vardtm separated by ","
from sampleID_database ;

[linlin87]

Thanks Tom.

The code is still not working. My 'want' has no observations:

data want;
	set have;
	sampleid=.;
	if "%scan(&vardtm,1,',')"dt le dtm le "%scan(&vardtm,2,',')"dt then sampleid='1438';
	if dtm>"%scan(&vardtm,2,',')"dt then sampleid='2348';
run;

I would expect that 

%put %scan(&vardtm,1,',');
%put %scan(&vardtm,2,',');

would output the first two dates in the macro list, but instead it just says:

A character operand was found in the %EVAL function or %IF
condition where a numeric operand is required. The
condition was: 02FEB20:15:20:00
ERROR: Argument 2 to macro function %SCAN is not a number.
ERROR: A character operand was found in the %EVAL function or %IF
condition where a numeric operand is required. The
condition was: 02FEB20:15:20:00
ERROR: Argument 2 to macro function %SCAN is not a number.

[PaigeMiller]

I think the solution is top replace the quotes around the commas with %str(,) and use %quote around &vardtm

such as

"%scan(%quote(&vardtm),1,%str(,))"dt

but as I said earlier, I think the whole thing could be programmed without macros and without macro variables, using data stored in SAS data sets (or external data bases). Doing this with macros just causes all sorts of issues (all of which can be overcome, but for beginners it isn't easy)

[linlin87]

Thanks Paige. 

It now works.

But, out of interest, how would you do this in the alternative way you described?

I guess it may be difficult to demonstrate without any concrete data.

I basically have a dataset with a long list of temperature recordings and the datetime of the recording. These recordings come from sequential samples (at specific points in time, the sample is changed). I then have a separate dataset which has the datetimes at which the sample was changed. So I want to assign the temperature recordings to the correct sample, if you see what I mean?

[Tom]

---

@linlin87 wrote:

Thanks Paige. 

It now works.

But, out of interest, how would you do this in the alternative way you described?

I guess it may be difficult to demonstrate without any concrete data.

I basically have a dataset with a long list of temperature recordings and the datetime of the recording. These recordings come from sequential samples (at specific points in time, the sample is changed). I then have a separate dataset which has the datetimes at which the sample was changed. So I want to assign the temperature recordings to the correct sample, if you see what I mean?

---

Just use SQL to join.

So if you have a list of dates with SAMPLEID attached and you want to combine it with another list of dates you should be able to just JOIN the two tables by the criteria.

So if SAMPLES is the dataset with the SAMPLEID values and OTHER is the table with the dates you want to match use something like:

proc sql;
create table want as
select a.*
     , b.sampleid
from other a
  left join samples b
  on a.date between b.date-7 and b.date+7 
;
quit;

Fix the join criteria to match your situation.

[PaigeMiller]

What about this rough outline of doing this without macros

proc sql;
    create table vardtm as
    select distinct sample_dtm 
    from sampleID_database;
quit;
proc transpose data=vardtm out=vardtm1 prefix=time;
    var sample_dtm;
run;
data want;
     if _n_=1 then set vardtm1;
	set have;
	sampleid=.;
	if time1 le dtm le time2 then sampleid='1438';
	if dtm>time2 then sampleid='2348';
run;

Naturally I can't test this because I don't have your data, but now the whole issue of writing macro code that works is gone, the problem caused by commas is gone, the data remains in SAS data sets instead of a long macro string that you have to pull apart. Please give this a shot and forget macros in this case.

[linlin87]

Thank you Paige and Tom for the alternative methods of solving this.

One question: I have to run this process numerous times for different files. In some files there are 3 samples (and therefore dates) and in other files there are more (up to 7). This changes as more data comes in for the samples, we may get more than 7 so I want to be flexible with this.

I don't want to have to keep checking this and hard coding:

time1 < dtm < time2
time2 < dtm < time3
time3 < dtm < time4
...

Which application would you recommend?

[PaigeMiller]

From your original code

	if %scan(&vardtm,2,',') le dtm le %scan(&vardtm,2,',') then sampleid='1438';
	if dtm>%scan(&vardtm,2,',') then sampleid='2348';

where are these sampleID values stored?

[Tom]

If you share some example data (mock something up that simulates your issues in as small a sample as possible) then you can get a better answer.

It looks perhaps like you are trying to assign the most recent value based on dates.

So if you have one dataset with sampleid and sampledate and you want to combine with another dataset with result dates you might get what you want by INTERLEAVING the two datasets.

So it might looks something like:

data want;
  set samples(in=in1) results(in=in2);
  by id date ;
  retain xx ;
  if first.id then call missing(xx);
  if in1 then xx=sampleid;
  if in2;
  sampleid=xx;
  drop xx;
run;

[Reeza]

How do you know which time goes with which sample ID?

How is that time data set structured? You show the macro variable just getting the time component so not sure how the rest of your program is structured. 

There are two options I would recommend:

• Create a format that groups each time into sampleIDs and maps the times to the samples using an INPUT function
• Restructure your time to SampleID data set to support a join.

I would not use macro's here as they're cumbersome, the logic is difficult to verify, test and maintain. 

If you can make some fake data of each data set and show how the data is structured we can show how the code should be designed. 

---

@linlin87 wrote:

Thank you Paige and Tom for the alternative methods of solving this.

One question: I have to run this process numerous times for different files. In some files there are 3 samples (and therefore dates) and in other files there are more (up to 7). This changes as more data comes in for the samples, we may get more than 7 so I want to be flexible with this.

I don't want to have to keep checking this and hard coding:

time1 < dtm < time2
time2 < dtm < time3
time3 < dtm < time4
...

Which application would you recommend?

---

[Tom]

%put %scan(&vardtm,1,',');
%put %scan(&vardtm,2,',');

Again look that what code you have asked the macro processor to generate.  In this case the evaluation of &VARDTM is generating code like:

%put %scan(01JAN22:01:02:03,02FEB21:00:00:00,1,',');

Which has way too many commas for the %SCAN() function.

Also why are you asking %SCAN() to use both comma and single quote as the delimiter characters?

You could use the second mistake to cancel the first by adding double quote characters to the list of delimiters.

%put %scan("&vardtm",1,",");

Now you are generating valid (if strange) code:

%put %scan("01JAN22:01:02:03,02FEB21:00:00:00",1,",");

Why not just use a different delimiter. Something that is not used by the macro language or the output of the datetime format.

So if you build the string with | as the delimiter then your code could be:

%put %scan(&vardtm,1,|);

Which will evaluate to this:

%put %scan(01JAN22:01:02:03|02FEB21:00:00:00,1,|);

[PaigeMiller]

Your %PUT statement fails because it sees the comma inside &vardtm and thinks this indicates the end of the first argument to %SCAN. In addition, if you are scanning for the text string ',' this doesn't exist in your &vardtm and so %SCAN fails for both reasons.

The whole issue of putting commas between items to separate the items causes issues in macro processing. Nevertheless, the solution is:

%put %scan(%quote(&vardtm),1,%str(,));

It seems to me that you make your life much easier by separating the date/time values with a space instead of a comma, but I'm skeptical that macro variables are needed here (more disucssion of that below)

Then there are also the problems mentioned by @Tom that have to be fixed even if you get the %SCAN to work right.

All of which indicates to me that macro variables are not the best solution here, that these date/time values exist in a data set and instead of turning them into a macro string that has to be pulled apart, you should find a way (if possible) to use the values in a data set without macro variables at all.

[linlin87]

Hi Paige,

If I now want to assign

%scan(%quote(&vardtm),1,%str(,))

as a date within a datastep, how do I refer to it?

data want;
set have;
format sample_dtm datetime16.;
sample_dtm = "%scan(%quote(&vardtm),2,%str(,))";
run;

Should I use input?

[PaigeMiller]

---

@linlin87 wrote:

Hi Paige,

If I now want to assign

%scan(%quote(&vardtm),1,%str(,))

as a date within a datastep, how do I refer to it?

data want;
set have;
format sample_dtm datetime16.;
sample_dtm = "%scan(%quote(&vardtm),2,%str(,))";
run;

Should I use input?

---

I gave an example, you extract the date/time values from the database into a SAS data set, transpose the data set, and then use it. The variables are now named TIME1 TIME2 ...

No macros or macro variables needed.