topic Re: Combine Multiple Tables and Add MMDD Date As Column in SAS Programming

Combine Multiple Tables and Add MMDD Date As Column

SASNovice55 — Wed, 12 Oct 2022 09:57:31 GMT

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

Re: Combine Multiple Tables and Add MMDD Date As Column

Patrick — Wed, 12 Oct 2022 11:53:34 GMT

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;

Re: Combine Multiple Tables and Add MMDD Date As Column

SASJedi — Wed, 12 Oct 2022 12:07:05 GMT

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

Re: Combine Multiple Tables and Add MMDD Date As Column

Reeza — Wed, 12 Oct 2022 14:19:43 GMT

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.

Re: Combine Multiple Tables and Add MMDD Date As Column

ballardw — Wed, 12 Oct 2022 15:12:49 GMT

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.);