topic Re: PROC SQL Do loop with case when in SAS Programming

PROC SQL Do loop with case when

elsfy — Thu, 29 Sep 2022 08:27:15 GMT

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

Re: PROC SQL Do loop with case when

LinusH — Thu, 29 Sep 2022 09:20:03 GMT

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.

Re: PROC SQL Do loop with case when

elsfy — Thu, 29 Sep 2022 09:37:39 GMT

I want to update a table with a variable coming from another table. Problem is that this variable can come from many datasets that have the same structure but each of these datasets are constructed for a specific date (e.g dataset name : BASE_201801 for jan,2018) that contain 2 variables : ID and quantity (the variable that i want to add). So to add this variable i have to match the ID but also the date of the observation in my main dataset with the date in the dataset name that have the variable that i want to add. So how can i specify that table A's date variable have to be equal to the date in the other dataset name ?

Re: PROC SQL Do loop with case when

elsfy — Thu, 29 Sep 2022 09:39:03 GMT

If possible, i don't want to merge all datasets that have the variable that i want to add to my main dataset

Re: PROC SQL Do loop with case when

ballardw — Thu, 29 Sep 2022 14:36:36 GMT

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

Re: PROC SQL Do loop with case when

Reeza — Thu, 29 Sep 2022 15:33:49 GMT

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?