BookmarkSubscribeRSS Feed
lior
Calcite | Level 5

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?

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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  &.

lior
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

lior
Calcite | Level 5

yes, I just figur it out myself...

thanks!

Reeza
Super User

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.)



lior
Calcite | Level 5

yes this was my mistake, I use the lable instead of the format .

thank you.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3719 views
  • 0 likes
  • 3 in conversation