Hi,
I have a dataset Q with Jan and Feb data (With duplicates). I have 2 month end datasets for Jan and Feb. I need to join the Jan data in Q to Jan ME and Feb data in Q to Feb ME. Variables in both ME datasets are named the same
Doing it separately in 2 steps is obviously easy but is it possible to do it in one proc sql step ? This would be helpful as the dataset Q gets populated with more months.
The ME sets are in format XY_20190131 and XY_20190228
Any direction would be appreciated.
It is not a good idea to have separate datasets for each month. But sometimes, that's what you get.
The easy way to handle this case may be to collect the monthly data in a data step view before joining, e.g.:
data XY_2019/View=XY_2019;
set inlib.XY_2019: indsname=dsname;
filedate=input(scan(dsname,2,'_'),yymmdd8.);
format filedate date9.;
run;
You can then join your Q table to that using a standard SQL left join.
Something like this:
proc sql;
create table abc as select a.variable1,a.variable2,b.variable3,c.variable4
from a left join b on a.variable1=b.variable1
left join c on a.variable1=c.variable1;
quit;
Thank you Paige,
The output I need would need to look like the End result table.
The orig table is the main table and there is a jan result and feb result table. I want to join to the respective month end tables based on the month listed in the original table on a.id = b.id or whatnot.
But can joining to 2 tables in step be done in one proc sql statement?
@TheNovice wrote:
But can joining to 2 tables in step be done in one proc sql statement?
I believe I already showed it could be done, and provided an example of code.
It is not a good idea to have separate datasets for each month. But sometimes, that's what you get.
The easy way to handle this case may be to collect the monthly data in a data step view before joining, e.g.:
data XY_2019/View=XY_2019;
set inlib.XY_2019: indsname=dsname;
filedate=input(scan(dsname,2,'_'),yymmdd8.);
format filedate date9.;
run;
You can then join your Q table to that using a standard SQL left join.
Thank you very much!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.