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
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
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?
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.
Dear Miller,
Many thanks. Next time i will try your indicative format
Best ,
George
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
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?
Dear Keintz
Thanks a million for your illustrative and simplified approach at the end!
Great!
Best regards,
George
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.