How to get at least one obs when there are no matching values in the dataset

[knveraraju91]

Dear,

The below code produces output :

adt     dom   var        paramcv

2     AA         ADT    ARDT

1      BB        BDT    BRDT

OUTPUT NEED in dataset five::

in data set four there are no OBS . When i used dataset four along with two and three to create five i got the above output.

But I need:

adt     dom   var        paramcv

2     AA         ADT    ARDT

1      BB        BDT    BRDT

.        EE       EDT     ERDT

Please help. Thank you

data one;
input a $ b c;
datalines;
aa 2 3
bb 1 3
cc 4 4
dd 5 6
;

data two(keep=adt dom var paramcd);
set one(where =(a='aa'));
adt=b;
dom="AA";
var="ADTC";
paramcd="ARDT"
;
run;
data three(keep=adt dom var paramcd);
set one(where =(a='bb'));
adt=b;
dom="BB";
var="BDTC";
paramcd="BRDT"
;
run;
data four(keep=adt dom var paramcd);
set one(where =(a='ee'));
adt=b;
dom="ee";
var="EDTC";
paramcd="ERDT"
;
run;

data five;
set two three four;
run; 

[novinosrin]

data one;
input a $ b c;
datalines;
aa 2 3
bb 1 3
cc 4 4
dd 5 6
;

data two(keep=adt dom var paramcd);
set one(where =(a='aa'));
adt=b;
dom="AA";
var="ADTC";
paramcd="ARDT"
;
run;
data three(keep=adt dom var paramcd);
set one(where =(a='bb'));
adt=b;
dom="BB";
var="BDTC";
paramcd="BRDT"
;
run;
data four(keep=adt dom var paramcd);
set one end=last;
retain f;
if a='ee' then do;
adt=b;
dom="ee";
var="EDTC";
paramcd="ERDT";
f=1;
end;
if last and not f then do;
adt=.;
dom="ee";
var="EDTC";
paramcd="ERDT";
output;
end;
run;

data five;
set two three four;
run; 

[Astounding]

Here's how I would modify your DATA FOUR step:

data four(keep=adt dom var paramcd);
if _n_=1 and done then output;
set one(where =(a='ee')) end=done;
adt=b;
retain dom "EE"
var "EDTC"
paramcd "ERDT"
;
output;
run;

I assume you meant to get "EE" rather than "ee".  But that's easy to change if it was not your intent.

[knveraraju91]

Dear,

Thank you for the help.  But I have one more question. The output in the data set seven from my code is:

id       ardt     brdt     

1         2           .

2         .            1

But as per my specs i have to write to code like this on data set seven.

data eight;

set seven;

if erdt is ne . and ardt ne . and brdt ne .then want=min(erdt,ardt,brdt);

run;

Because the dataset four did not have any OBS, I am not getting the variable erdt in the  dataset seven after proc transpose.  Could please help how to approach in this case.

output that i need after final step is so i can use my specs code With my code the  erdt variable not available  in dataset seven. Thank you

id       ardt     brdt    erdt 

1         2           .         .

2         .            1        .

data one;
input id a $ b c;
datalines;
1 aa 2 3
2 bb 1 3
3 cc 4 4
4 dd 5 6
;

data two(keep=id adt dom var paramcd);
set one(where =(a='aa'));
adt=b;
dom="AA";
var="ADTC";
paramcd="ARDT"
;
run;
data three(keep=id adt dom var paramcd);
set one(where =(a='bb'));
adt=b;
dom="bb";
var="BDTC";
paramcd="BRDT"
;
run;
data four(keep=id adt dom var paramcd);
set one(where =(a='ee'));
adt=b;
dom="ee";
var="EDTC";
paramcd="ERDT"
;
run;

data five;
set two three four;
run;

data sl;
input id tr$ ag se$;
datalines;
1 p 23 m
2 o 45 f
3 i 34 m
4 u 53 f
5 y 33 m
;
proc sort data=five;
by id;
run;
proc sort data=sl;
by id;
run;
data six;
merge sl(in=a) five(in=b);
by id;
if b;
run;
proc transpose data=six out=seven(drop=_name_ _label_);
	by id;
	id paramcd;
	var adt;
run;

[Astounding]

A key part of the reason is that you are using your original code to create data set FOUR.  You didn't replace that step with the suggested code.  If you fix your original problem, the results will change.

[knveraraju91]

Thank you very much for the reply.

With your suggested code I am getting the output where the first obs is (id=dot.). I need output without first obs ( without id=dot)                id       ardt     brdt    erdt 

.           .            .         .

1         2           .         .

2         .            1        .

output need;

id       ardt     brdt    erdt 

1         2           .         .

2         .            1        .

Thank you

[art297]

Then can't you just exclude that record from you final datastep? e.g.:

proc transpose data=six out=seven(where=(not missing(id)) drop=_:);
    by id;
    id paramcd;
    var adt;
run;

Art, CEO, AnalystFinder.com