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
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.
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?
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.
Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.
Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.
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.
Ready to level-up your skills? Choose your own adventure.