SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
michael1212
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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
michael1212
Calcite | Level 5
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!
PaigeMiller
Diamond | Level 26

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
michael1212
Calcite | Level 5
I'll do that.
Thanks for the help!

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 984 views
  • 1 like
  • 2 in conversation