Transfer macro variable to a query after rsubmit statement

[georgel]

Dear  SAS users, 

I would like to know how to resolve the issue below:

%let psw=xxxxxxxx;
%let username=xxxxxx;

/* 	Push the macro variables to the remote server (without the next line the WRDS server 
	would not 'understand' &vars etc); */
%let wrds = wrds.wharton.upenn.edu 4016;options comamid = TCP remote=WRDS;
signon  noscript user='xxxx'  password="&psw";


 %let one_day_ago_fmt = 
  %sysfunc(
   intnx(
   day,             /* unit of time interval */
   %sysfunc(today()), /* function to get current date */
   -3,                /* number of intervals, negative goes to the past */
   same               /* alignment of interval date. "Same" is for same day of month */
   ), YYMMDDN8. /*date9.*/  /* Tell %SYSFUNC how to format the result */
  );


%syslput one_day_ago_fmt=&one_day_ago_fmt ;
 %put &=one_day_ago_fmt;
 

rsubmit;
libname comp '/wrds/comp/sasdata/d_na';




/*Get returns after conditional exercise date*/
PROC SQL;
       create table firms_us_current as
       select *
       from  comp.secd a
  	  where a.DATADATE=&one_day_ago_fmt;/*'29Jul2022'd*/
quit;

 %put &=six_mo_ago_fmt;

proc sort data=firms_us_current out=firms_us_current nodupkeys;
     by  conm ;
run;

proc download data=firms_us_current out=firms_us_current ;run;
endrsubmit;
signoff;

This code is running to get remotely data for stock prices from compustat but actuallly the date is not captured by the macro variable and the data returns no observations. 

Please let me know how to address this issue.

Many thanks in advance 

Regards, 

George

[PaigeMiller]

Show us the ENTIRE log for this code (every single line, every single character in the log for this code; do not select parts of the log to show us)

Copy the log as text and paste it into the window that appears when you click on the </> icon.

In the future, when code isn't working, show us the log as explained here.

[georgel]

Dear Miller, 

Many thanks. Next time i will try your indicative format

Best , 

George

[mkeintz · Accepted solution]

Because you specified YYMMDDN8. as the format for the %sysfunc function, you are uploading macrovar one_day_ago_fmt as the character value 20220729.

The value is treated as a numeric in the a.datadate=20220729 filter in your SQL.  Which actually means you are looking for a date that is 20220729 days after Jan 1, 1960  (i.e. over 55,000 years from now).

You need to filter for a.datadate='29JUL2022'd  (or better yet for macro usage   "29JUL2022"d).

So

1. Change the output format of the SYSFUNC to DATE9.
   
   
2. Change a.datadate=&one_day_ago_fmt  to
      a.datadate="&one_day_ago_fmt"d

Editted addition:  Also you can make your program faster by converting your two steps to one step - i.e. do both the filtering and sorting in the same step.  In your case, I would recommend:

proc sort data=comp.secd out=firms_us_current  ;
  where datadate="&one_day_ago_fmt"d;
  by conm;
run;

This writes your subset of SECD to disk only once.  It's efficient because the WHERE filter is implemented by the data engine, so PROC SORT never sees the unwanted observations.

BTW, why did you use NODUPKEYS in your proc sort?  How would PROC SORT know which company record for a given CONM you actually want?

[georgel]

Dear Keintz

Thanks a million for your illustrative and simplified approach at the end!

Great!

Best regards, 

George