subsetting dataset in a loop macro

[Gigiwen]

Hi, this seems a naive question: the code looks simple enough but I could not get it right. I suspect somethign wrong with the "date" variable as well the coding within the MACRO.

I have a dataset and I have a list of dates. I wish to subset by the list of dates. In my macro, I plan to take the information up to 01/01/2018 and run regressions. Then next loop take 01/02/2018 and run regressions so on. So, the loop structure i have is as follows:

%macro outPhi(raw);
    %do i=1 %to 6;

        proc sql ;
            select Date into :Date from DateList where n=&i;
        quit;

/* Step 1: Read Chop the sampel and estimate it */
	data temp;
	set &raw;
	if date < &Date;
	run;
/* Step 2: Run regression - omitted */
		
    %end;
%mend;

%outPhi(OneStock);

I can see that in each loop it works as expected: take the first observation from DateList and then the second....

But I can also see the DATA step is unsuccessuful because the dataset has 0 observations. What's wrong here?

NOTE: There were 2327 observations read from the data set WORK.ONESTOCK.
NOTE: The data set WORK.TEMP has 0 observations and 10 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

[Tom]

What TYPE of variable is DATE in DATELIST?  Does it have a FORMAT attached to it? What format?

What TYPE of variable is DATE in ONESTOCK?  Does it have a FORMAT attached to it? What format?

The problem is probably because you are putting the FORMATTED value into the DATE macro variable.  So unless DATE in DATELIST has NO format attached to it (which would make the values hard for humans to read) then resulting value will not work to generate the SAS code you need in the WHERE statement.

So assuming both variable are actual DATE values (numeric with a format like DATE or YYMMDD that work on DATE values) then you can either put the raw number into the macro variable.  Or use the DATE format to put the date formatted in a style the DATE informat can understand and enclose the value in quotes and append the letter d to create a date literal.

Run some test:

data test;
  date=date();
  format date yymmdd10.;
run;

proc sql noprint;
select date format=32. 
     , date format=date9.
  into :date1 trimmed
     , :date2 trimmed
  from test
;
quit;

data test2;
  set test;
  if date=&date1 then put date= "matches &date1";
  if date="&date2"d then put date= "matches '&date2'd.";
run;

Result:

133  data test2;
134    set test;
135    if date=&date1 then put date= "matches &date1";
136    if date="&date2"d then put date= "matches '&date2'd.";
137  run;

date=2024-03-29 matches 23464
date=2024-03-29 matches '29MAR2024'd.
NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST2 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

[Gigiwen]

Both has FORMAT DDMMYY10.

[Tom]

---

@Gigiwen wrote:
Both has FORMAT DDMMYY10.

---

Which means you would have generated code like:

where date < 29/03/2024

Which will compare the number of days since 1960 that is stored in DATE with the result of dividing 29 by 3 and then dividing that by 2,024.

[Patrick]

You could use code along the line of below.

libname source "c:\temp";
%let chunks=6;

data work.start_end_dt;
  format start_dt end_dt date9.;
  keep start_dt end_dt;
  chunk=ceil(nobs/&chunks);
  do i=1 to nobs;
    set source.datelist nobs=nobs end=last;
    if mod(i,chunk)=1 then start_dt=date;
    if mod(i,chunk)=0 or last then 
      do;
        end_dt=date;
        output;
      end;
  end;
run;

%macro demo(start,stop);
  /* here your regression code */
  proc sql;
    select count(*) as n_rows
    from source.onestock
    where date between &start and &stop;
  quit;
%mend;

filename codegen temp;
data _null_;
  file codegen;
  /*file print;*/
  set work.start_end_dt;
  put '%demo(' start_dt 6. ',' end_dt 6. ');';
run;
%include codegen / source2;

 Or same done via a single data step.

libname source "c:\temp";

%macro demo(start,stop);
  /* here your regression code */
  proc sql;
    select count(*) as n_rows
    from source.onestock
    where date between "&start"d and "&stop"d;
  quit;
%mend;

%let chunks=6;
filename codegen temp;
data _null_;
  file codegen;
  /*file print;*/
  chunk=ceil(nobs/&chunks);
  do i=1 to nobs;
    set source.datelist nobs=nobs end=last;
    if mod(i,chunk)=1 then start_dt=date;
    if mod(i,chunk)=0 or last then 
      do;
        put '%demo(' start_dt date9. ',' date date9. ');';
      end;
  end;
run;
%include codegen / source2;

Execution log:

55         %include codegen / source2;
NOTE: %INCLUDE (level 1) file CODEGEN is file 
      C:\Users\<user>\AppData\Roaming\SAS\EnterpriseGuide\EGTEMP\SEG-16992-42d0c766\contents\SAS Temporary 
      Files\_TD18708_<user>3_\#LN00081.
56        +%demo(01NOV2018,01SEP2019);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds
      

57        +%demo(01OCT2019,01AUG2020);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
.... and so on ...

[Sajid01]

Hello @Gigiwen 
Please consider the following example code. This should work for you.
It works with the datasets you attached. Make changes as needed.

The code here create one dataset for each date value in your datelist dataset
This is for illustration only. You can create only one dataset which is recreated in each iteration.
I have commented out your code.

 %macro outPhi(raw);
%do i=1 %to 6;
/*
proc sql ;
select Date into :Date from DateList where n=&i;
quit;
*/   
/* Create a temporary dataset to hold date value.
The code here create one dataset for each date value.
This is for illustration only.
You can create only one dataset which is recreated in each iteration*/
 */   
data temp_&i.;
set DateList;
if _n_=&i. then output;
run;
/* Step 1: Read Chop the sample and estimate it */
	/*data temp;
	set &raw;
	if date < &Date;
	run;
	*/
/* Select what you need*/
	proc sql;
	create table temp&i. as
	select a.* from &raw as a, temp_&i. b
	where a.date < b .date;
	quit;
/* Step 2: Run regression - omitted */
		
    %end;
%mend;

%outPhi(OneStock);

[PaigeMiller · Accepted solution]

---

@Gigiwen wrote:

Hi, this seems a naive question: the code looks simple enough but I could not get it right. I suspect somethign wrong with the "date" variable as well the coding within the MACRO.

I have a dataset and I have a list of dates. I wish to subset by the list of dates. In my macro, I plan to take the information up to 01/01/2018 and run regressions. Then next loop take 01/02/2018 and run regressions so on. So, the loop structure i have is as follows:

%macro outPhi(raw);
    %do i=1 %to 6;

        proc sql ;
            select Date into :Date from DateList where n=&i;
        quit;

/* Step 1: Read Chop the sampel and estimate it */
	data temp;
	set &raw;
	if date < &Date;
	run;

---

Great example of Maxim 28. "Macro variables should not be formatted". In this case, if you extract the macro variable &Date without the format, everything should work. By adding format=9.0 to your SQL, you remove the format from &date, now &date is an integer representing the unformatted SAS date, and then the rest of the code works. Why? Because when SAS works with date values, it ALWAYS uses the unformatted date to do arithmetic or logical comparisons, regardless of how it is formatted.

proc sql ;
       select Date format=9.0 into :Date from DateList where n=&i;
quit;