Creating a Report from SAS Dataset

[hexx18]

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

[hexx18]

            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

[Reeza]

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

[hexx18]

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

[Reeza]

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?

[hexx18]

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;

[Reeza]

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.

[hexx18]

Can you please suggest some ideas on how to approach this

[Reeza]

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.

[hexx18]

Sure I will do that

[hexx18]

Hi Reeza,

I have attached test data for Dataset xyz . 

[Reeza]

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

[ballardw]

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.

[hexx18]

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

[Cynthia_sas]

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