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

Super Contributor
Posts: 325

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

Dear,

The below code produces output :

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:

1      BB        BDT    BRDT

.        EE       EDT     ERDT

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

set one(where =(a='aa'));
dom="AA";
paramcd="ARDT"
;
run;
set one(where =(a='bb'));
dom="BB";
var="BDTC";
paramcd="BRDT"
;
run;
set one(where =(a='ee'));
dom="ee";
var="EDTC";
paramcd="ERDT"
;
run;

data five;
set two three four;
run; ```

Super User
Posts: 2,068

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

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

set one(where =(a='aa'));
dom="AA";
paramcd="ARDT"
;
run;
set one(where =(a='bb'));
dom="BB";
var="BDTC";
paramcd="BRDT"
;
run;
set one end=last;
retain f;
if a='ee' then do;
dom="ee";
var="EDTC";
paramcd="ERDT";
f=1;
end;
if last and not f then do;
dom="ee";
var="EDTC";
paramcd="ERDT";
output;
end;
run;

data five;
set two three four;
run; ``````
Super User
Posts: 6,935

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

[ Edited ]

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

Super Contributor
Posts: 325

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

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'));
dom="AA";
paramcd="ARDT"
;
run;
data three(keep=id adt dom var paramcd);
set one(where =(a='bb'));
dom="bb";
var="BDTC";
paramcd="BRDT"
;
run;
data four(keep=id adt dom var paramcd);
set one(where =(a='ee'));
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;
run;``````

Super User
Posts: 6,935

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

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.

Super Contributor
Posts: 325

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

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

Super User
Posts: 8,220

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

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;