Creating a Report from SAS Dataset

[Cynthia_sas]

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

[hexx18]

Yes exactly right .

[hexx18]

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 ?

[Reeza]

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

[hexx18]

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;

[Reeza]

I can read code, I'm asking for the business logic.

[hexx18]

 Hi Reeza,

Thanks for all your help . It is to identify unique id in 2014 , unique id in 2015 and unique id in both 2014 and 2015 .

[Reeza]

Unique as in never seen before 2014 or Unique as # of unique users in 2014?

[hexx18]

Its Unique users in 2014 .

[Reeza · Accepted solution]

Are the numbers accurate with this code and that data.

data temp;
set xyz;
length type $8.;;
if CC=1 then do; type = 'CC'; output; year = 9999; output; end;
if BB=1 then do;type = 'BB'; output; year = 9999; output; end;
if BB=1 & CC=1 then do;type = 'Both'; output; year = 9999; output; end;
drop cc bb;
run;

proc sql;
create table want as
select year, type, count(distinct ID) as UniqueIDs
from temp
group by year, type;
quit;

proc print data=want;
run;