Combine Multiple Tables and Add MMDD Date As Column

[SASNovice55]

Hi All,

I have several tables named, test_table_0511, test_table_0518 etc. The tables are labelled at 7 day intervals in the format MMDD. All columns are the same.

I want to just join them all together in a master table and have a date column which specifies from the end of the table name what the date was when the table was made.

I don't know SAS well (at all) and so far I just used proc sql to manually union them all together which would take too long for how many I have:

PROC SQL;

SELECT *, '0511' AS Date  FROM test_table_0511 

UNION

SELECT *, '0518' AS Date  FROM test_table_0518 

etc etc 

run;

Expected Output 

Master Table:

Var 1	Var 2	Var 3	Date
58.96%	44.58%	78.49%	0511
59.45%	6.69%	23.92%	0511
57.12%	62.65%	58.64%	0511
69.28%	82.95%	80.13%	0511
71.47%	30.57%	40.17%	0511
45.57%	39.50%	89.19%	0518
74.68%	24.12%	98.68%	0518
89.52%	16.49%	64.53%	0518
56.59%	98.23%	72.71%	0518
6.95%	29.76%	0.96%	0518
11.92%	62.55%	59.95%	0525
86.99%	3.39%	79.00%	0525
59.97%	42.44%	80.71%	0525
45.70%	82.67%	95.15%	0525
43.74%	15.02%	24.36%	0525

Anyone have any suggestions?

Thanks very much 

[Patrick · Accepted solution]

Below one way how this could work.

I called the date column you want mmdd and it is a string and not a SAS date value because you would also need the year information to really create a SAS date value.

I've added some logic to drop table names where the mmdd string would resolve to a real SAS Date value if adding a year. This also shows you how you could create SAS Date value in case you know the year and could just hardcode it in your script.

/* create sample data */
data test_table_0511 test_table_0518 test_table_all;
  set sashelp.class;
run;

data want;
  length _curr_tbl $41 mmdd $4;
  set test_table_: indsname=_curr_tbl;
  mmdd=scan(_curr_tbl,-1,'_');
  if missing(input(cats('2004',mmdd),?? yymmdd8.)) then delete;
run;

[SASJedi]

My two cent's worth:

/*Build test data sets*/
data  test_table_0511 
		test_table_0518 
		test_table_0525 
		test_table_0531;
	array var(3);
	format var: percent6.2;
	do _z=1 to 5;
		do _i=1 to dim(var);
			var[_i]=round(rand('uniform'),.0001);
		end;
		output;
	end;
	/* Drop all variables with a name beginning with '_'*/
	drop _:;
run;

data want;
	set test_table_0511 
		 test_table_0518 
		 test_table_0525 
		 test_table_0531 indsname=thisTable;
	/* This date is just a character variable */
	Date=scan(thisTable,-1,'_');

	/* Or, create a real SAS date using the month and day 
	   from the file name and the current year */
	/* Extract the month and day from the file name */
	_m=input(substr(scan(thisTable,-1,'_'),1,2),2.);
	_d=input(substr(scan(thisTable,-1,'_'),3),2.);
	/* Convert _m and _d to numeric, grab the current year */
	SASDate=MDY(_m,_d,year(today()));
	format SASDate mmddyy10.;
	/* Drop all variables with a name beginning with '_'*/
	drop _:;
run;

Result:

Obs	var1	var2	var3	Date	SASDate
1	53%	6.3%	41%	0511	05/11/2022
2	36%	53%	45%	0511	05/11/2022
3	3.3%	42%	9.6%	0511	05/11/2022
4	18%	13%	92%	0511	05/11/2022
5	90%	1.8%	41%	0511	05/11/2022
6	53%	6.3%	41%	0518	05/18/2022
7	36%	53%	45%	0518	05/18/2022
8	3.3%	42%	9.6%	0518	05/18/2022
9	18%	13%	92%	0518	05/18/2022
10	90%	1.8%	41%	0518	05/18/2022
11	53%	6.3%	41%	0525	05/25/2022
12	36%	53%	45%	0525	05/25/2022
13	3.3%	42%	9.6%	0525	05/25/2022
14	18%	13%	92%	0525	05/25/2022
15	90%	1.8%	41%	0525	05/25/2022
16	53%	6.3%	41%	0531	05/31/2022
17	36%	53%	45%	0531	05/31/2022
18	3.3%	42%	9.6%	0531	05/31/2022
19	18%	13%	92%	0531	05/31/2022
20	90%	1.8%	41%	0531	05/31/2022

[ballardw]

Shorter code though obfuscating a detail or two perhaps:

If I want to set a specific year:

   date=input(cats(scan(thisTable,-1,'_'),'2022'),mmddyy10.);

Or assuming the year that the code is run

   date=input(cats(scan(thisTable,-1,'_'),year(today())),mmddyy10.);

[Reeza]

As other have demonstrated, the INDSNAME option within the SET statement captures the input data set name and then you can parse the name to get the date out.