topic Re: Select first records from the dataset in SAS Programming

Select first records from the dataset

cho16 — Tue, 09 Aug 2022 17:02:23 GMT

Hi ..

Need a help on this.

I have a dataset with the following attributes: id, typ(XN, IN), and sysdat.
If an ID contains both XN and IN, only XN records should be retrieved.
If the ID contains XN records, only XN records must be retrieved.
If the ID conatins IN records , only IN records must be retrieved.

Input :

ID typ sysdat

123 IN 2210128

123 XN 2410128

124 XN 2210128

125 IN 2210128

Ouput :

ID typ sysdat

123 IN 2210128

124 XN 2210128

125 IN 2210128

Thanks in Advance..

Re: Select first records from the dataset

Tom — Tue, 09 Aug 2022 17:18:40 GMT

Your rules don't make sense. The first IF is a subset of the last IF. Perhaps you meant to include ONLY in the last one like you did for the middle one?

But also your expected results do not match the rules.

So 123 has both so only the XN observation should be kept, but your output is showing that one of the two IN observations was kept instead.

And 125 has only IN observations, but you only selected one of them. How did you decide which one to keep?

Re: Select first records from the dataset

cho16 — Tue, 09 Aug 2022 17:23:45 GMT

Sorry typo mistake

If an ID contains both XN and IN, only IN records should be retrieved.

Re: Select first records from the dataset

cho16 — Tue, 09 Aug 2022 17:24:19 GMT

Sorry typo mistake...If an ID contains both XN and IN, only XN records should be retrieved.

Select a first observations from the data

cho16 — Tue, 09 Aug 2022 19:01:02 GMT

Hi ..

Need a help on this.

I have a dataset with the following attributes: id, typ(XN, IN), and sysdat.
If an ID contains both XN and IN, only IN records should be retrieved.
If the ID contains XN records, only XN records must be retrieved.
If the ID conatins IN records , only IN records must be retrieved.

Input :

ID typ sysdat

123 IN 2210128

123 XN 2410128

124 XN 2210128

125 IN 2210128

Ouput :

ID typ sysdat

123 IN 2210128

124 XN 2210128

125 IN 2210128

Thanks in Advance..

Re: Select first records from the dataset

PeterClemmensen — Tue, 09 Aug 2022 19:02:10 GMT

I'm as confused about the rules here as @Tom, so this is somewhat a shot.

data have;
input ID typ $ sysdat;
datalines;
123 IN 2210128 
123 IN 2210128 
123 XN 2410128 
124 XN 2210128 
125 IN 2210128 
125 IN 2210128 
;

proc sql;
   create table want as
   select distinct * from have 
   group by ID
   having typ = min(typ)
   ;
quit;

Result:

ID   typ  sysdat
123  IN   2210128  
124  XN   2210128 
125  IN   2210128

Re: Select a first observations from the data

PeterClemmensen — Tue, 09 Aug 2022 19:03:30 GMT

I'm as confused about the rules here as @Tom in the other thread.

However, try this.

data have;
input ID typ $ sysdat;
datalines;
123 IN 2210128 
123 IN 2210128 
123 XN 2410128 
124 XN 2210128 
125 IN 2210128 
125 IN 2210128 
;

proc sql;
   create table want as
   select distinct * from have 
   group by ID
   having typ = min(typ)
   ;
quit;

Result:

ID   typ  sysdat
123  IN   2210128  
124  XN   2210128 
125  IN   2210128

Re: Select first records from the dataset

andreas_lds — Wed, 10 Aug 2022 04:50:34 GMT

I have merged your posts. Please don't double-post question.

Re: Select first records from the dataset

Kurt_Bremser — Wed, 10 Aug 2022 05:47:05 GMT

@cho16 wrote:
Sorry typo mistake...If an ID contains both XN and IN, only XN records should be retrieved.

Then your output for 123 is wrong.

Please review your requirements and expected output so they make sense. In particular, define a selection rule when multiple observations meet the condition.

Re: Select first records from the dataset

Ksharp — Wed, 10 Aug 2022 12:26:11 GMT

data have;
input ID typ $ sysdat;
datalines;
123 IN 2210128 
123 IN 2210128 
123 XN 2410128 
124 XN 2210128 
125 IN 2210128 
125 IN 2210128 
;

proc sql;
create table want as
select distinct *,case when count(distinct typ)>1 and typ='IN' then 1
when count(distinct typ)=1 then 1 else 0 end as flag
 from have
  group by id
   having calculated flag=1
;
quit;

Re: Select a first observations from the data

mkeintz — Wed, 10 Aug 2022 13:39:20 GMT

For each ID, interleave all the observations such that any "IN" records will precede any "XN" records. Then just keep the first individual record for each ID:

data have;
input ID typ $ sysdat;
datalines;
123 IN 2210128 
123 IN 2210128 
123 XN 2410128 
124 XN 2210128 
125 IN 2210128 
125 IN 2210128 
run;
data want;
  set have (where=(typ='IN'))
      have (where=(typ='XN'));
  by id;
  if first.id;
run;

This assumes dataset HAVE is sorted by ID. But it doesn't matter what the order is within each ID.