topic Re: Table Structure to replicate in SAS in SAS Programming

Table Structure to replicate in SAS

Anto180788 — Mon, 11 Jul 2022 09:28:10 GMT

Good Morning everyone,

I'm trying to replicate a table structure in SAS as in the example of table structure attached.

I would to know if it is possibile to replicate this structure in SAS and how.

The output expected is the table structure in the example with subtitles, if it possibile.

Thank you for all advice.

Re: Table Structure to replicate in SAS

andreas_lds — Mon, 11 Jul 2022 10:54:27 GMT

I don't open attachments, but replicating an excel table with sas is almost always a question of how much time one has. With the Report Writing Interface you can do anything, but you will have to code it.

Re: Table Structure to replicate in SAS

Anto180788 — Mon, 11 Jul 2022 11:08:03 GMT

Good Morning andreas,
I can look the attachment and I have to zoom to see the structure. Let me know if you can't do it.
I have time. So is the Report Writing interface only way to do that?
Have you look the example?
Thanks

Re: Table Structure to replicate in SAS

andreas_lds — Mon, 11 Jul 2022 11:50:39 GMT

I won't open attachments due to security rules in my company. So i don't know if using an output procedure (report, tabulate) can create the table you want.

Re: Table Structure to replicate in SAS

Sajid01 — Mon, 11 Jul 2022 12:17:00 GMT

Your excel file is apparently a report generated from some other applications.
It is indeed possible to produce similar reports using proc tabulate or proc report and combine ods excel destination.
The look and file will largely be identical but may not necessaril be exactly the same.

Re: Table Structure to replicate in SAS

Anto180788 — Mon, 11 Jul 2022 12:46:16 GMT

Hi Sajid,
thanks to reply me.
The point is to replicate the structure of table. In particular, I need to replicate row and columns structure(with specific spaces). So do you think is it possibile with proc tabulate and proc report?
Thanks

Re: Table Structure to replicate in SAS

Anto180788 — Mon, 11 Jul 2022 12:46:56 GMT

ah ok, don't worry Andreas. Thanks

Re: Table Structure to replicate in SAS

Kurt_Bremser — Mon, 11 Jul 2022 12:58:14 GMT

Don't expect a 1:1 copy of the look, but you can (almost) always create the same logical structure. To give you hints how to do it, we need to have usable source data (SAS dataset, posted as a data step with datalines) and the logic for calculating and/or setting the values in the report.

Re: Table Structure to replicate in SAS

Anto180788 — Mon, 11 Jul 2022 13:22:57 GMT

Hi Kurt,
For now, I need only replicate only the structure.
I haven't the data yet. As you can understand, this is a structure of a template. It is a final step of a elaboration process.

Re: Table Structure to replicate in SAS

Sajid01 — Mon, 11 Jul 2022 13:46:39 GMT

If your intention is to create a dataset with same structure as in the excel file, I am afraid, no. There is no easy way to do it.
You can , however, import the excel file and its content will constitute the data of the dataset.

Re: Table Structure to replicate in SAS

Kurt_Bremser — Mon, 11 Jul 2022 13:51:45 GMT

To get from A to B, one needs to know both A and B.

So for providing examples of code to create the report (B), we need to know the structure of the source data (A); without it, there's nothing to suggest, except "PROC REPORT might do it" or something similar vague.

Re: Table Structure to replicate in SAS

Anto180788 — Mon, 11 Jul 2022 16:27:08 GMT

That is my intention.
The excel fle is an output of a process. So importing back it is useless.

Re: Table Structure to replicate in SAS

Anto180788 — Mon, 11 Jul 2022 16:29:24 GMT

The excel file is an output.
So, do you want to know what are datatasets in input?
Thanks

Re: Table Structure to replicate in SAS

Kurt_Bremser — Mon, 11 Jul 2022 16:40:55 GMT

The Excel spreadsheet you show us is clearly a report, and not a dataset or a database table. If you created it from SAS datasets, you already have everything you need.

Datasets or tables in databases have consistent types for columns and only one set of names for the columns.

In SAS, if you want to store such a report, you store the underlying dataset(s) and the SAS program, so you can recreate the report anytime by rerunning the program.

Re: Table Structure to replicate in SAS

ghosh — Mon, 11 Jul 2022 16:47:16 GMT

This structure is quite simple to replicate with a proc report inside an ods excel structure. imcluding multisheet reports. You can also create any printer ourtput such as html, pdf, etc.

You could strart with this link, and also read up on the papers available on this topic on the SAS Global Forum

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm#n0bmw0v2sv7lepn17aovz1217r9k

Do have a specific requirement?

Re: Table Structure to replicate in SAS

Sajid01 — Mon, 11 Jul 2022 16:59:48 GMT

Thanks @Anto180788 for offering to post the datasets.
However the report is the outcome of a series of steps.
One needs to know the process ( BRD- Business Requirement Document) and the data to write the proper and relevant code.
The following is a good introduction to SAS Proc Report procedure and give a fairly good idea about what can be accomplished http://www.scsug.org/wp-content/uploads/2017/10/An-Introduction-to-PROC-REPORT-SCSUG-2017.pdf

Re: Table Structure to replicate in SAS

Anto180788 — Tue, 12 Jul 2022 10:36:32 GMT

Good morning everyone,

many thanks for all advice.

My boss said me that the problem is to replicate cell unified (yellow area) in SAS.

Is there anyway to do that?

Secondo question, Is it possibile to use a structure of table and just put inside a dataset?

Thank you very much

Re: Table Structure to replicate in SAS

Kurt_Bremser — Tue, 12 Jul 2022 13:54:47 GMT

@Anto180788 wrote:

Good morning everyone,

many thanks for all advice.

My boss said me that the problem is to replicate cell unified (yellow area) in SAS.

Is there anyway to do that?

PROC REPORT can set background colors for each column, and you can also (in a COMPUTE block) set attributes (including the background) for a single report item depending on position or content.

Secondo question, Is it possibile to use a structure of table and just put inside a dataset?

Please explain what you mean by that.

SAS provides metadata in the SQL DICTIONARY tables, so you can retrieve that from there.

If, OTOH, you want to create the structure in a dataset without having actual data, you can do it like this:

data class;
set sashelp.class;
stop;
run;

The resulting dataset will have all columns from SASHELP.CLASS, but zero observations.

Re: Table Structure to replicate in SAS

Anto180788 — Tue, 12 Jul 2022 14:20:48 GMT

Hi Kurt,
thank you for your answer.
1) In proc report, can you create exactly a column with a space as in example?
2) I mean, I want use that table strucutre (initially empty) and put data inside. As an insert in sql.

Re: Table Structure to replicate in SAS

Tom — Tue, 12 Jul 2022 14:45:10 GMT

@Anto180788 wrote:
Hi Kurt,
thank you for your answer.
1) In proc report, can you create exactly a column with a space as in example?
2) I mean, I want use that table strucutre (initially empty) and put data inside. As an insert in sql.

No. You are still confusing the REPORT that you want to create in the Excel file and the DATA that you will need to create it.