BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Saba1
Quartz | Level 8

Hi

 

I am trying to get a data set (New) with a new column (col_new) created by little calculation in proc sql. I am using two existing data sets (have1 and have2) and doing the calculation for the observations where year and month of date columns (date1 and date2) are matched. Below is the code:

 

proc sql; 
 create table New
   as select a.*, round((a. col1*a. col2)/b. col3, 1) as col_new
    from have1 as a, have2 as b
     where year(a. date1) = year(b. date2) and month(a. date1) = month(b. date2);
quit;

This code works well and gives no error. But I want to make sure that is it fine to mention "a" and "b" with dates inside the parenthesis i.e. year (a. date1) or month (b. date2)? Or it should be written as below:

where a. year(date1) = b. year(date2) and a. month(date1) = b. month(date2);

Please guide me in this regard. Thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

The correct way to use the date functions such as YEAR or MONTH is almost as you have it in the first bit of code:

 

where year(a.date1) = year(b.date2) and month(a.date1) = month(b.date2);

HOWEVER you cannot have spaces between the alias (a. b. references) and the variable name.

 

.

View solution in original post

4 REPLIES 4
ballardw
Super User

The correct way to use the date functions such as YEAR or MONTH is almost as you have it in the first bit of code:

 

where year(a.date1) = year(b.date2) and month(a.date1) = month(b.date2);

HOWEVER you cannot have spaces between the alias (a. b. references) and the variable name.

 

.

Saba1
Quartz | Level 8

@ballardw That is great. Thank you so much for the guidance. As far as the space b/w a. b. references and variable names is concerned, I guess this has to do something with my SAS program settings because these references change color (i.e. turn green) only when I give a space, otherwise their font color remains unchanged (i.e. black). Anyhow, I will still give it a try without any space.
Thanks

PGStats
Opal | Level 21

The green color tells you that SAS considers the name a. or b. as a format name.

PG
Saba1
Quartz | Level 8

@PGStats Right. But for me these a. and b. turn green only when I give a space between them and variable name (the way i have shown in the code above). Is it ok?

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 1019 views
  • 0 likes
  • 3 in conversation