topic Re: How to force output in proc sql / data merge in SAS Procedures

How to force output in proc sql / data merge

SMartin — Tue, 18 Nov 2014 14:27:22 GMT

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!

Silke

Re: How to force output in proc sql / data merge

RW9 — Tue, 18 Nov 2014 15:52:40 GMT

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.

Re: How to force output in proc sql / data merge

SMartin — Tue, 18 Nov 2014 16:01:47 GMT

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...

Re: How to force output in proc sql / data merge

RW9 — Tue, 18 Nov 2014 16:09:22 GMT

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;

Re: How to force output in proc sql / data merge

SMartin — Tue, 18 Nov 2014 16:41:33 GMT

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.

Re: How to force output in proc sql / data merge

RW9 — Wed, 19 Nov 2014 09:15:03 GMT

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;

Re: How to force output in proc sql / data merge

SMartin — Tue, 25 Nov 2014 14:50:17 GMT

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