- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello!
I hope this isn't too difficult - but I'm having a hard time working out what I need to do in order to accomplish this:
I have a list of tables (for each year) which get created in various parts of my code that are currently hard-coded which I would like to be able to create using a macro (I think) based on a list of years which i determine at the start of my code.
The reason for this is I will need to re-run based on updated data and different years in the future and I don't want to have to edit every instance where I have hard-coded years into table creation.
For example I have:
PROC SQL;
DROP TABLE t_1819, t_1920, t_2021;
CREATE TABLE t_1819
(Group VARCHAR (2)
,Income_band_1819 SMALLINT
,BHC_Frequency DECIMAL (10)
,AHC_Frequency DECIMAL (10)
,BHC_Gross DECIMAL (10)
,AHC_Gross DECIMAL (10)
);
CREATE TABLE t_1920
(Group VARCHAR (2)
,Income_band_1819 SMALLINT
,BHC_Frequency DECIMAL (10)
,AHC_Frequency DECIMAL (10)
,BHC_Gross DECIMAL (10)
,AHC_Gross DECIMAL (10)
);
CREATE TABLE t_2021
(Group VARCHAR (2)
,Income_band_2021 SMALLINT
,BHC_Frequency DECIMAL (10)
,AHC_Frequency DECIMAL (10)
,BHC_Gross DECIMAL (10)
,AHC_Gross DECIMAL (10)
);
Is there a way in which I can have a list of years at the start of my code (e.g. 1819, 1920, 2021, ... ) and then one process to create all tables by year in the one go? While also updating columns e.g. 'Income_band_ ...' with each year?
I hope that's clear - I'm just not sure what it is that I'm doing here.
Many thanks,
Michael
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Create one table, then export to Excel with different tabs by year, very simple. You would use ODS EXCEL to obtain tabs by year. Example:
ods excel file="abcdef.xlsx" options(sheet_interval='BYGROUP' sheet_label='YEAR');
proc print data=have;
by year;
var x1 x2 x3 ... ; /* Put your real variable names here */
run;
ods excel close;
As you can see, no macros needed.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It is likely one large table, with a variable representing the year is a better way to go. But its not clear to me how you would create these tables, do you really want to use your SQL code, or will the tables be created some other way?
Once you have these tables by year, what are you going to do with them?
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It doesn't matter a lot to me how these are created, this is just how I have been creating them throughout the code. My processing also inserts the values based on the tables by year. I could change this if what you are suggesting makes more sense.
In the end they are exported to excel with each table (year) going into a different tab. I think I would like the end product to end up exporting in that way.
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Create one table, then export to Excel with different tabs by year, very simple. You would use ODS EXCEL to obtain tabs by year. Example:
ods excel file="abcdef.xlsx" options(sheet_interval='BYGROUP' sheet_label='YEAR');
proc print data=have;
by year;
var x1 x2 x3 ... ; /* Put your real variable names here */
run;
ods excel close;
As you can see, no macros needed.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the help!