BookmarkSubscribeRSS Feed
SMartin
Calcite | Level 5

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1208 views
  • 3 likes
  • 2 in conversation