topic Re: Merge Table Headers in SAS Programming

Merge Table Headers

maricelj — Fri, 24 Sep 2021 05:17:29 GMT

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.

Re: Merge Table Headers

andreas_lds — Fri, 24 Sep 2021 05:31:02 GMT

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.

Re: Merge Table Headers

maricelj — Fri, 24 Sep 2021 05:55:54 GMT

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.

Re: Merge Table Headers

Tom — Fri, 24 Sep 2021 06:19:41 GMT

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;

Re: Merge Table Headers

Kurt_Bremser — Fri, 24 Sep 2021 07:03:01 GMT

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.

Re: Merge Table Headers

maricelj — Fri, 24 Sep 2021 07:07:16 GMT

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.

Re: Merge Table Headers

maricelj — Fri, 24 Sep 2021 07:20:31 GMT

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

Re: Merge Table Headers

Kurt_Bremser — Fri, 24 Sep 2021 07:22:22 GMT

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.

Re: Merge Table Headers

Kurt_Bremser — Fri, 24 Sep 2021 07:32:04 GMT

@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.

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;

Re: Merge Table Headers

maricelj — Fri, 24 Sep 2021 07:38:32 GMT

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.

Re: Merge Table Headers

maricelj — Fri, 24 Sep 2021 07:40:10 GMT

Hi,

Yes. Thank you so much.

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

Re: Merge Table Headers

Kurt_Bremser — Fri, 24 Sep 2021 07:48:26 GMT

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.

Re: Merge Table Headers

maricelj — Fri, 24 Sep 2021 07:56:17 GMT

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