Merge Table Headers

[maricelj]

Hi there!
I need your help.
I have this table and the headers contain year with its months next to it. Is there a way to do it wherein the output would be in the created table?
Sample output be like this:
Name | Item | Year 2021
| | 01 | 02 | 03 | 04 | 05 | 06| 07| 08 | 09| 10 | 11| 12

Hope to hear from you.
Thank you and keep safe.

[andreas_lds]

Please post an excerpt of the data you have as working data step and re-format the layout you want by posting it using "insert code", so that blanks aren't deleted.

[maricelj]

Hi,

Thank you.

The code goes like this:

PROC SQL;
CREATE TABLE Test AS
SELECT DISTINCT
ID as ID, Name
TRANS_MONTH
FROM Transaction
WHERE year = '2021'
ORDER BY ID;
QUIT;

I want the output to have a year above each trans_month but such year should appear only once and no idea how to do it.

PROC SQL;
	CREATE TABLE Test AS 
		SELECT DISTINCT
			ID, Name
			TRANS_MONTH
			FROM Transaction
			WHERE year = '2021'
			ORDER BY ID;
QUIT;

Output be like this:
ID | NAME |                            YEAR 2021
   |      | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12

Thank you.

Thank you.

[Tom]

So you don't want to produce a dataset at all.  What you are showing is a REPORT.

So perhaps something like this?

proc report data=transaction ;
  where year='2021';
  column id name year,trans_month;
  define id / group;
  define name / group;
  define year / across;
run;

[maricelj]

Hi,

Sorry if it's kind of confusing.

I just can't add a table here so I made such presentation which seemed to be a report.

Actually, it's not yet a report instead, it's a dataset.

It's a table and I want the column headers be like that, if it's possible.

My report is in excel format which such table is being called through a macro.

Thank you.

[Kurt_Bremser]

You can always add a table here. You already gave us the column names, so those should not be sensitive.

Make up some fake data, like

data have;
input id $ name $ trans_month;
datalines;
XXX YYYY 1
XXX YYYY 2
;

that is representative of what you really have. Don't forget to include "edge" cases, like certain missing values etc.

[maricelj]

Hi,

Yes. Thank  you so much.

Sorry for if i made some confusions with regards to my previous posts.

[Kurt_Bremser]

You show us code (that won't run, BTW, because of a missing comma), but not data.

Pleaee give us examples of your three variables, preferrably in a data step with datalines.

[maricelj]

Hi,

Sorry for that.

data Test;
  input ID Name _01 _02 _03 _04 _05 _06 _07 _08 _09 _10 _11 _12;
  cards;
1 a 10 1 2 3 1 4 5 4 5 1 5 1
2 b 12 3 3 4 5 6 5 4 6 5 4 2
3 c 2 2 1 3 1 3 4 5 2 3 5 3
;

Is there a way that I can add label which is the year (2021) on top of the months?

Thank you.

maricelj

[Kurt_Bremser]

---

@maricelj wrote:

Is there a way that I can add label which is the year (2021) on top of the months?

---

Not in a dataset; datasets do not have the concept of column-spanning labels, but reports have.

Like

data Test;
  input ID $ Name $ _01 _02 _03 _04 _05 _06 _07 _08 _09 _10 _11 _12;
  cards;
1 a 10 1 2 3 1 4 5 4 5 1 5 1
2 b 12 3 3 4 5 6 5 4 6 5 4 2
3 c 2 2 1 3 1 3 4 5 2 3 5 3
;

proc transpose data=test out=long;
by id name;
var _:;
run;

proc report data=long;
column id name col1,_name_;
define id /group;
define name / group;
define col1 / "" analysis;
define _name_ / "2021" across;
run;

[maricelj]

Hi,

Oh I see. Thank you.

By the way, is there a way to import such report in an excel format?

I am very much overwhelmed because of the fast response I am getting here. Thank you very much.

Sorry if I made some confusions with regards to my previous posts.

Keep safe.

[Kurt_Bremser · Accepted solution]

First, let's get our wording straightened up. When we ("the SAS people") say IMPORT, we mean to bring external data into the SAS environment. So we import Excel data into SAS. When we create Excel files from SAS data, we call than an EXPORT.

Similarly, the procedures often used for this are also called IMPORT and EXPORT, respectively.

To get a report to Excel, just use ODS (the Output Delivery System):

ods excel file="$HOME/report.xlsx";

proc report data=long;
column id name col1,_name_;
define id /group;
define name / group;
define col1 / "" analysis;
define _name_ / "2021" across;
run;

ods excel close;

Use a path name proper for your environment in the FILE= option of the ODS statement.

[maricelj]

Hey there,
Yeah. I mean export.
Thanks for your help.
Got it and it works!
Please keep safe.