How to force output in proc sql / data merge

[SMartin]

Hi,

I want to create a report with customer value groups, count customers in branch and total share percent of customers - like this:

cvg      percent      branch   count

A          .011          nord      135

B          .022          nord       .           --> this coloums should be output in my report!!! Either with a missing sign or with a zero

C          .033          nord       21

A          .011          south     51

B          .022          south      .

C          .033          south       .

A          .011          west     5261

B          .022          west      56

C          .033          west     2155

A          .011          east       10

B          .022          east        .

C          .033          east      635

The percentage is always the same, because it is the percentage of total share (in this customer value group). Some customer value groups are empty, because this branch hasn't customers in this customer value group.

I will force sas to output a column with a missing value or any entry (e. g. 0), like in the table above.

But sas do not output this colums. Here is a functioning example:

data kunden;

   infile datalines delimiter=',';

   input kwg $1. kunde_key filiale $4.;

   datalines;                     

A, 1230, Nord

A, 1231, Nord

B, 1232, Nord

A, 1233, Nord

C, 1234, Nord

D, 1235, Nord

C, 1236, Sued

D, 1237, Sued

C, 1238, West

C, 1239, West

A, 12310, West

A, 12311, West

B, 12312, West

C, 12313, East

C, 12314, East

;

proc sql;

create table anz_kun as

  select

  kwg,

  filiale,

   count(distinct kunde_key) as Anzahl_Kunden

  from kunden

  group by filiale, kwg;

quit;

data anteil;

   infile datalines delimiter=',';

   input kwg $1. anteil;

   datalines;                     

A, 0.1013

B, 0.2021

C, 0.3046

D, 0.0455

;

proc sort data=anz_kun; by kwg; run;

proc sort data=anteil; by kwg; run;

data anteil_kunden;

merge anteil(in=a) anz_kun(in=b);

by kwg;

if a;

run;

I need help... how can I handle this problem?

Thanks a lot!

BR

Silke

[RW9]

Sorry, this is confusing me.  In your example output of:

cvg      percent      branch   count

A          .011          nord      135

B          .022          nord       .           --> this coloums should be output in my report!!! Either with a missing sign or with a zero

C          .033          nord       21

I see none of these columns in either of the two input datasets, nor a calculation of how to arrive at any of the above.  Please clarify input data, versus output data.

[SMartin]

This is my problem. I want this as output:

cvg      percent      branch   count

A          .011          nord      135

B          .022          nord       .           --> this coloums should be output in my report!!! Either with a missing sign or with a zero

C          .033          nord       21

But my real data creates an output (as it is shown in the example), which has only the first and last column. SAS don't creat the column with the missing value, because in the data 'kunden' there aren't B customers in the branch nord. I want to force SAS not to delete this column, but to output it with a missing value.

I hope this helps...

[RW9]

But it does contain data for B / Nord:

B, 1232, Nord

Anyways, if you want to have groups which are not in your data then add in a sort of template file:

data template;

     length cvg $3;

     cvg="A"; output;

     cvg="B"; output;

     cvg="C"; output;

run;

proc sql;

     create table WANT as

     select     A.*,

                   COALESCE(A.CVG,B.CVG) as CVG

     from       YOUR_DATA A

     full join   TEMPLATE B

     on          A.CVG=B.CVG;

quit;

[SMartin]

Yes, sorry. I typed an output, which doesn't match with my (input) example data... It was just for showing, how I want it to look...

But your answer doens't solve my problem. An empty cvg doesn't appear in the output data.

[RW9]

Please run this code, it will show 3 rows in the ouput with B having missing result.  This is what I mean.

data have;
  cvg="A"; result=1; output;
  cvg="C"; result=3; output;
run;
data template;
  do cvg="A","B","C";
    output;
  end;
run;

proc sql;
  create table WANT as
  select  COALESCE(A.CVG,B.CVG) as CVG,
          A.RESULT
  from    WORK.HAVE A
  full join WORK.TEMPLATE B
  on      A.CVG=B.CVG;
quit;

[SMartin]

Hi,

thanks for your answer! It work's in the example, but it didn't solve my problem in my real data... the row with the missing is still missing...

I'm still trying...

BR Silke