topic Re: Creating a Report from SAS Dataset in ODS and Base Reporting

Creating a Report from SAS Dataset

hexx18 — Mon, 26 Jul 2021 18:24:26 GMT

Hi ,

I have two SAS Datasets Test1 and Test2 which looks like below and I want to create below report Series_all from the below two SAS Datasets . Can anyone please help ?

Test 1

Obs _TYPE_ _FREQ_ SN_CCY1 SN_CCY2 PP_CC SN_BBY1 SN_BBY2 PP_BB SN_Pairy1 SN_pairy2 SN_pairy1y2
1 0 100 10 20 30 40 50 60 235 540 900

Test2

Obs _TYPE_ _FREQ_ SN_CCY1 SN_CCY2 PP_CC SN_BBY1 SN_BBY2 PP_BB SN_Pairy1 SN_pairy2 SN_pairy1y2
1 0 750 200 250 630 100 150 400 544 326 125

Series_all Report

	Series Id yearly
	AOS	LOS
	CC	BB	Paired	CC	BB	Paired
Total Series Id, 2014	10	40	235	200	100	544
Total Series Id, 2015	20	50	540	250	150	326
Total Series Id every year	30	60	900	630	400	125

Re: Creating a Report from SAS Dataset

hexx18 — Mon, 26 Jul 2021 19:04:58 GMT

Basically report looks like below

                                            Series Id yearly
                                            AOS                     LOS
                                            CC BB Paired    CC   BB   Paired
Total Series Id, 2014         10 40     235        200 100 544
Total Series Id, 2015          20 50 540           250 150 326
Total Series Id every year 30 60 900           630 400 125

Re: Creating a Report from SAS Dataset

Reeza — Mon, 26 Jul 2021 19:09:16 GMT

1. Append your two data sets together. If they have the same names this is trivial. This looks a lot like output from a procedure so you may want to explore how to get that in one table at the start as well.
2. If you have two, how do you get that third row? Clarify the logic.

Use PROC PRINT or REPORT to display the data.
https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/259-30.pdf

Re: Creating a Report from SAS Dataset

Reeza — Mon, 26 Jul 2021 19:09:51 GMT

If that BB column is Unique count you will need to summarize it separately as well, and append that in.

Re: Creating a Report from SAS Dataset

ballardw — Mon, 26 Jul 2021 19:10:35 GMT

Incomplete descriptions of your data.

What is an AOS or LOS? Where does that information come from? Same with "Series Id".

You show 3 variables in each set that have CC as part of the name but no description as to which one goes in which column of the report or how. Similar issue with BB and Pair.

Please look at your "want" table and see if it actually looks as intended. Things posted into the message windows are often reformatted and may not be as you intended.

I am going to guess that for a single report table that you want to 1) combine the data to a single data set and 2) likely want to set a variable with the value of AOS and LOS based on which data set, or maybe that sets the "series Id" values.

Re: Creating a Report from SAS Dataset

hexx18 — Mon, 26 Jul 2021 19:56:21 GMT

ALL SN_CCY1 , SN_BBY1 , SN_Pairy1 all these values should be under Total Series Id, 2014 and also under CC,BB,Paired respectively (10,40,235)

ALL SN_CCY2 , SN_BBY2 , SN_Pairy2 all these values should be under Total Series Id, 2015 and also under CC,BB,Paired respectively (20,50,540)

ALL PP_CC , PP_BB , SN_PairY1y2 all these values should be under Total Series Id, every year and also under CC,BB,Paired respectively

All above reading should be under AOS heading

I am manually typing in excel for these headings CC,BB,Paired , AOS , Total Series Id, 2014 , Total Series Id, 2015 , Total Series Id, every year

Is there a way to put all those values from two SAS Datsets to the report and report code should automatically but the headings

Exactly same for LOS also but under heading LOS

Can you please help ? Thank you

Re: Creating a Report from SAS Dataset

hexx18 — Mon, 26 Jul 2021 20:09:22 GMT

Hi ,

No SAS Dataset has these headings

I am manually typing in excel for these headings ( CC,BB,Paired , AOS , LOS Total Series Id, 2014 , Total Series Id, 2015 , Total Series Id, every year). Then putting values from SAS Dataset Test1 . AOS when it says SN_CCY1 then inserting into CC 2014 and so on ..

		Series Id yearly
	AOS			LOS

	CC	BB	Paired	CC	BB	Paired
Total Series Id, 2014	values with suffix y1 values fall here
Total Series Id, 2015	values with suffix y2 fall here
Total Series Id every year

Re: Creating a Report from SAS Dataset

Reeza — Mon, 26 Jul 2021 20:27:34 GMT

Instead of this, can you show the source data you used to generate these summaries and how you did that? I suspect this may be easier in a different format and you're definitely using untidy data structures.

Would it be correct to assume that generally the suffix 1 = 2014, 2 = 2015 for example and no suffix = Overall?

Re: Creating a Report from SAS Dataset

hexx18 — Mon, 26 Jul 2021 21:22:27 GMT

Hi Reeza,

Below is the code which generates TEST1 Dataset

DATA TEST56(KEEP=

SN_CCY1

SN_CCY2

SN_BBY1

SN_BBY2

SN_Pairy1

SN_Pairy2

PP_CC

PP_BB

SN_Pairy1Y2

);

SET XYZ END = EOF;

BY id ;

RETAIN CCY1 CCY2 BBY1 BBY1 ;

IF FIRST.id THEN

DO;

BBY1 = 'N';

BBY2 = 'N';

CCY1 = 'N';

CCY2 = 'N';

END;

IF CC GT 0 AND YEAR = 2014 THEN CCY1 = 'Y' ;

IF CC GT 0 AND YEAR = 2015 THEN CCY2 = 'Y' ;

IF BB GT 0 AND YEAR = 2014 THEN BBY1 = 'Y' ;

IF BB GT 0 AND YEAR = 2015 THEN BBY2 = 'Y' ;

IF LAST.id OR EOF THEN

DO;

SN_CCY1 = 0;

SN_CCY2 = 0;

PP_CC = 0;

SN_BBY1 = 0;

SN_BBY2 = 0;

PP_BB = 0;

SN_Pairy1 = 0;

SN_Pairy2 = 0;

SN_Pairy1Y2 = 0;

IF CCY1 = 'Y' THEN SN_CCY1 = 1;

IF CCY2 = 'Y' THEN SN_CCY2 = 1;

IF CCY1 = 'Y' AND CCY2 = 'Y' THEN PP_CC = 1;

IF BBY1 = 'Y' THEN SN_BBY1 = 1;

IF BBY2 = 'Y' THEN SN_BBY2 = 1;

IF BBY1 = 'Y' AND BBY2 = 'Y' THEN PP_BB = 1;

IF CCY1 = 'Y' OR BBY1 = 'Y' THEN SN_Pairy1 = 1;

IF CCY2 = 'Y' OR BBY2 = 'Y' THEN SN_Pairy2 = 1;

IF PP_CC = 1 or PP_BB = 1

THEN SN_Pairy1Y2 = 1;

OUTPUT TEST56;

END;

RUN;

PROC SUMMARY DATA = test56;

VAR SN_CCY1

SN_CCY2

PP_CC

SN_BBY1

SN_BBY2

PP_BB

SN_Pairy1

SN_Pairy2

SN_Pairy1Y2

;

OUTPUT OUT = TEST1

SUM= ;

RUN;

PROC PRINT DATA = TEST1;

RUN;

Re: Creating a Report from SAS Dataset

Cynthia_sas — Mon, 26 Jul 2021 21:40:28 GMT

Hi:

Based on your first posting, It looks to me like this:

But I still don't understand how the data gets summarized like this as 2 separate datasets.

Cynthia

Re: Creating a Report from SAS Dataset

Reeza — Mon, 26 Jul 2021 21:42:42 GMT

Ok. Here's the thing to get this data into a format for the report you want you will need to restructure your data sets and then report on them. Given what you've posted I suspect that's an inefficient process, but we don't have anything else to go on. So you can either go with this system and restructure your output and get what you need. Or you can go back to the start and redesign your process more efficiently from the start.

Re: Creating a Report from SAS Dataset

hexx18 — Mon, 26 Jul 2021 22:22:11 GMT

Can you please suggest some ideas on how to approach this

Re: Creating a Report from SAS Dataset

Reeza — Mon, 26 Jul 2021 22:28:24 GMT

You need to pick an approach first: "So you can either go with this system and restructure your output and get what you need. Or you can go back to the start and redesign your process more efficiently from the start."

If you want to redesign you need to provide significantly more information and data. Preferably sample data that aligns with the output - fake data is fine.

Re: Creating a Report from SAS Dataset

Cynthia_sas — Tue, 27 Jul 2021 13:56:14 GMT

Hmmm, actually, I think it looks more like THIS if you map from the original files to the desired output.

It looks like the TEST1 data populates the first 3 columns for each row and the TEST2 data populates the last 3 columns for each row, but the variable names determine which row the value belongs to (determines the year).This is awkward because that means you have to restructure/rename the data for it to be usable. It's almost a transpose or maybe 2 transposes. It complicates things that the same variable names are used for different columns in the final output.

Cynthia

Re: Creating a Report from SAS Dataset

hexx18 — Tue, 27 Jul 2021 01:52:23 GMT

Yes exactly right .

Re: Creating a Report from SAS Dataset

hexx18 — Tue, 27 Jul 2021 01:52:56 GMT

Sure I will do that

Re: Creating a Report from SAS Dataset

hexx18 — Tue, 27 Jul 2021 06:03:08 GMT

Hi Reeza,

I have attached test data for Dataset xyz .

Re: Creating a Report from SAS Dataset

hexx18 — Tue, 27 Jul 2021 15:58:42 GMT

Hi Cynthia ,
I can rename them CC_AOS , BB_AOS . Also , I can create a dataset which includes headings Total Series Id 2014, Total Series Id 2015 like that . I have attached sample data Can you please help with the report ?

Re: Creating a Report from SAS Dataset

Reeza — Tue, 27 Jul 2021 16:00:01 GMT

From the Excel file, what is the logic for a couple of the columns?

Re: Creating a Report from SAS Dataset

hexx18 — Tue, 27 Jul 2021 16:38:34 GMT

Hi Reeza,

After we run test data Test56 would have below columns with 0 and 1's and we do proc summary on those columns . I can rename the columns in the output data as well as in the report like CC_AOS .

SN_CCY1

SN_CCY2

SN_BBY1

SN_BBY2

SN_Pairy1

SN_Pairy2

PP_CC

PP_BB

SN_Pairy1Y2

DATA TEST56(KEEP=

SN_CCY1

SN_CCY2

SN_BBY1

SN_BBY2

SN_Pairy1

SN_Pairy2

PP_CC

PP_BB

SN_Pairy1Y2

);

SET XYZ END = EOF;

BY id ;

RETAIN CCY1 CCY2 BBY1 BBY1 ;

IF FIRST.id THEN

DO;

BBY1 = 'N';

BBY2 = 'N';

CCY1 = 'N';

CCY2 = 'N';

END;

IF CC GT 0 AND YEAR = 2014 THEN CCY1 = 'Y' ;

IF CC GT 0 AND YEAR = 2015 THEN CCY2 = 'Y' ;

IF BB GT 0 AND YEAR = 2014 THEN BBY1 = 'Y' ;

IF BB GT 0 AND YEAR = 2015 THEN BBY2 = 'Y' ;

IF LAST.id OR EOF THEN

DO;

SN_CCY1 = 0;

SN_CCY2 = 0;

PP_CC = 0;

SN_BBY1 = 0;

SN_BBY2 = 0;

PP_BB = 0;

SN_Pairy1 = 0;

SN_Pairy2 = 0;

SN_Pairy1Y2 = 0;

IF CCY1 = 'Y' THEN SN_CCY1 = 1;

IF CCY2 = 'Y' THEN SN_CCY2 = 1;

IF CCY1 = 'Y' AND CCY2 = 'Y' THEN PP_CC = 1;

IF BBY1 = 'Y' THEN SN_BBY1 = 1;

IF BBY2 = 'Y' THEN SN_BBY2 = 1;

IF BBY1 = 'Y' AND BBY2 = 'Y' THEN PP_BB = 1;

IF CCY1 = 'Y' OR BBY1 = 'Y' THEN SN_Pairy1 = 1;

IF CCY2 = 'Y' OR BBY2 = 'Y' THEN SN_Pairy2 = 1;

IF PP_CC = 1 or PP_BB = 1

THEN SN_Pairy1Y2 = 1;

OUTPUT TEST56;

END;

RUN;

PROC SUMMARY DATA = test56;

VAR SN_CCY1

SN_CCY2

PP_CC

SN_BBY1

SN_BBY2

PP_BB

SN_Pairy1

SN_Pairy2

SN_Pairy1Y2

;

OUTPUT OUT = TEST1

SUM= ;

RUN;

PROC PRINT DATA = TEST1;

RUN;