Hi,
How can i loop this ?
proc sql ; create table want as select a.* , CASE WHEN a.date = b.date then b.RD WHEN a.date = c.date then c.RD ELSE 0 END AS RD from list a left join base_201801 b on a.ID= b.iD /* Jan,2018 */ left join base_201802 c on a.ID = c.ID /* Feb, 2018*/ /* and so on with bases until 202209 corresponding to Sept, 2022*/ ; quit ;
This code is working but i want to add many more conditions to the CASE WHEN statement like if the date in b is not found in a then look into table c, else look in table d, etc
Sure, from a syntax perspective you can add more tables and extend your CASE statement.
You encapsulate it into a macro to make it more dynamic.
But looking at your code and data structure, I wondering what you actually want to achieve?
If you have tables segmented by month (but with the same structure), joins are usally not the most efficent way of working.
@elsfy wrote:
If possible, i don't want to merge all datasets that have the variable that i want to add to my main dataset
This join is likely to take much more time to accomplish that APPENDING (merge is side-by-side, append is vertical stacking) your data sets together, not to mention that you have to account for changes in the code when the next monthly data set gets created.
You have a data design that makes it difficult to look up the desired information.
As posted in your previous question here, the best solution is to combine the tables and then merge the data in.
https://communities.sas.com/t5/SAS-Programming/ADD-value-from-other-tables/m-p/835691
If you want an optimal methodology it would be to first look at all the months in the dataset and then create a view of those tables.
Then merge with the appended version.
Doing a join where you're searching more tables will be more inefficient than appending and is more cumbersome to code so not sure how this is a better solution.
Will a record be in more than the month indicated?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: