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

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

knveraraju91 — Sun, 15 Apr 2018 21:42:36 GMT

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;

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

novinosrin — Sun, 15 Apr 2018 22:03:57 GMT

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;

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

Astounding — Sun, 15 Apr 2018 22:33:05 GMT

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.

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

knveraraju91 — Mon, 16 Apr 2018 00:43:13 GMT

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;

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

Astounding — Mon, 16 Apr 2018 01:51:03 GMT

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.

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

knveraraju91 — Mon, 16 Apr 2018 02:28:39 GMT

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

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

art297 — Mon, 16 Apr 2018 03:12:40 GMT

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