topic Re: How to create macro or loop based on a list of values? in SAS Programming

How to create macro or loop based on a list of values?

michael1212 — Thu, 30 Jun 2022 13:09:45 GMT

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

Re: How to create macro or loop based on a list of values?

PaigeMiller — Thu, 30 Jun 2022 13:17:49 GMT

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?

Re: How to create macro or loop based on a list of values?

michael1212 — Thu, 30 Jun 2022 13:28:52 GMT

Hi,
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!

Re: How to create macro or loop based on a list of values?

PaigeMiller — Thu, 30 Jun 2022 13:46:17 GMT

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.

Re: How to create macro or loop based on a list of values?

michael1212 — Thu, 30 Jun 2022 14:00:16 GMT

I'll do that.
Thanks for the help!