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