Hello, I tried to produce a "left join" procedure as follow:
proc sql;
create table reg as
select a.*,b.company_price
from invest1 a
left join invest2 b
on put(a. b_s_d_w, three_days_befor_report_day.) eq put (b.DATADATE, date.)
order by GVKEY, b_s_d_w;
quit;
run;
I'm getting this error message: " The format THREE_DAYS_BEFOR_REPORT_DAY was not found or could not be loaded."
The format of the variable " three_days_befor_report_day" is YYMMDDN8. Type: Num .
The informat is F8.
Those format and informat are identical to other variables which I perform on them "left join" and succeed, that why I don't understand why this time I have no success.
Any ideas?
There are a few things I can see wrong here off the bat. Firstly you have a space between a. and b_s_d_w.
Secondly what do you mean by "The format of the variable " three_days_befor_report_day" is YYMMDDN8. Type: Num ."
Is three_days_befor_report_day
and actual format, created by proc format? Is it spelt correctly as e is missing from before. Is the length of the format name acceptable, I assumed there was an 8 char limit, but that might be old SAS.
Alternatively, I could also guess that three_days_befor_report could be a macro variable, if so It needs an ampersand in front of it &.
Hi,
Regarding the space, I try to also run the statement without space and recieve the same error messegae.
Regarding the variable: the variable name is "b_s_d_w" and its lable is "three_days_befor_report_day" the limit for label is 256 char...
This variable is a variable that I created and its base on a different variable that I recievd from a university database, RDQ:
data want;
set have;
b_s_d_w=RDQ-3;
format b_s_d_w ddmmyy8.;
run;
in order to recive the informat of the variable I run the statement:
data reg;
set reg;
informat b_s_d_w F8.;
run;
I did it because I read in this forum that sometimes when the format and informat of the variable is incorrect one will recieve the error message :
: " The format was not found or could not be loaded.", so I delibaretly use format and informat which work for me before in "left put" statements/
Also the format and informat of RDQ (the original variable) is YYMMDDN8. Type: Num .The informat is F8. so I thought the new variable format and informat should be the same.
Well there's your problem. You don't use a put() function and provide a variable and label, but a variable and a format.
on put(a. b_s_d_w, three_days_befor_report_day.) eq put (b.DATADATE, date.)
This should then be:
on put(a.b_s_d_w,date9.) eq put (b.DATADATE,date9.)
I.e. put the numeric value found in b_s_d_w into the structure DDMONYYYY, same for datadate, then match the two.
yes, I just figur it out myself...
thanks!
The following statement is where you're problem originates, I think.
It attempts to format a.b_s_d_w variable with the format three_days_before_report_day but you've indicated that three_days_befor_report_day is a variable not a format? Perhaps you're applying the wrong format?
put(a. b_s_d_w, three_days_befor_report_day.)
yes this was my mistake, I use the lable instead of the format .
thank you.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.