DATA Step, Macro, Functions and more

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

Reply
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 :

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; 

 

Super User
Posts: 2,068

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

Posted in reply to knveraraju91
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; 
Super User
Posts: 6,935

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

[ Edited ]
Posted in reply to knveraraju91

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.

Super Contributor
Posts: 325

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

Posted in reply to Astounding

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;

   

Super User
Posts: 6,935

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

Posted in reply to knveraraju91

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

Posted in reply to Astounding

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

Posted in reply to knveraraju91

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

 

Ask a Question
Discussion stats
  • 6 replies
  • 151 views
  • 3 likes
  • 4 in conversation