topic Re: SUBSETTING in SAS Procedures

SUBSETTING

robertrao — Wed, 11 Dec 2013 17:14:48 GMT

Hi Team,

I have data like shown.

the data is sorted already and if there first change of the codes variable i want to stop there and dont want records after that...

After acheiving that i want the first one of the first codes and the last one as shown

HAVE:

ID TIME CODES

101 02JAN2013 :18:30 EMNG

101 02JAN2013:18:47 EMNG

101 02JAN2013 :18:47 EMNG

101 02JAN2013:23:08 OMNG

101 02JAN2013:23:08 LMNG

101 02JAN2013:23:59 DMNG

101 02JAN2013:23:59 PLNH

WANT:

101 02JAN2013 :18:30 EMNG

101 02JAN2013:23:08 OMNG

Thanks

Re: SUBSETTING

ballardw — Wed, 11 Dec 2013 18:08:16 GMT

Is there a reason you Want example doesn't include the LMNG, DMNG or PLNH codes?

Re: SUBSETTING

robertrao — Wed, 11 Dec 2013 18:30:02 GMT

I just wanted UNTIL the first change

Thanks

Re: SUBSETTING

Reeza — Wed, 11 Dec 2013 18:39:42 GMT

Step 1 - enumerate your groups, count the groups and then the records within those.

Step 2 - determine criteria that meet your conditions

Step 3 - include those on a where clause to get your output.

There are fancier ways, but for a beginner level this is the easiest method to understand.

SAS FAQ: How can I create an enumeration variable by groups?

Re: SUBSETTING

Kavitha — Wed, 11 Dec 2013 18:49:29 GMT

Hi, can you please explain your requirement in a more detailed way

why your want data set does not have

101

02JAN2013:23:08

LMNG

Re: SUBSETTING

robertrao — Wed, 11 Dec 2013 19:08:56 GMT

Hi Kavitha,

Initial IS EMNG and the first change(in code) from there is OMNG.after that i dont want any information.

i need to know what the first change is?

Thanks

Re: SUBSETTING

Tal — Thu, 12 Dec 2013 04:31:35 GMT

so you only need the 1st records of the 1st two by groups. I would first take the 1st record of each by group(codes) and then narrow the result to the 1st two records.

But i am sure the experts could come up with some fancy ways of doing it

Re: SUBSETTING

barchan — Thu, 12 Dec 2013 10:11:47 GMT

The following code extracts the very first record and the first record after change of CODES:

data want(drop=xcode flag);

length xcode $4;

do until(last.id);

set have;

by id;

if first.id then do;

output;

xcode=codes;

end;

if codes^=xcode then do;

if ^flag then output;

flag=1;

end;

run;

Re: SUBSETTING

data_null__ — Thu, 12 Dec 2013 11:52:06 GMT

data codes;
   input ID:$3.   TIME & datetime.  CODES :$4.; 
   format time datetime.; 
   cards; 
101     02JAN2013 :18:30                       EMNG
101     02JAN2013:18:47                         EMNG
101     02JAN2013 :18:47                       EMNG
101     02JAN2013:23:08                      OMNG
101     02JAN2013:23:08                      LMNG
101     02JAN2013:23:59                     DMNG
101     02JAN2013:23:59                     PLNH
;;;;
   run; 
proc print; 
   run; 
data first2;
   set codes;
   by id codes notsorted; 
   if first.id then c=0; 
   if first.codes then c+1; 
   if c le 2 and first.codes then output; 
   drop c;
   run; 
proc print; 
   run; 

Re: SUBSETTING

Amir — Thu, 12 Dec 2013 13:13:05 GMT

Hi,

Another way, amongst many:

data want(drop=first_code changed_code);

set have;

output;

first_code=codes;

do until(changed_code);

set have;

changed_code=(first_code ne codes);

if changed_code then

do;

output;

stop;

end;

run;

Regards,

Amir.

Re: SUBSETTING

Kavitha — Thu, 12 Dec 2013 16:45:46 GMT

Hi Pls try the below code....

data have;

input ID:$3. TIME & datetime. CODES :$4.;

format time datetime.;

cards;

101 02JAN2013 :18:30 EMNG

101 02JAN2013:18:47 EMNG

101 02JAN2013 :18:47 EMNG

101 02JAN2013:23:08 OMNG

101 02JAN2013:23:08 LMNG

101 02JAN2013:23:59 DMNG

101 02JAN2013:23:59 PLNH

102 02JAN2013 :18:30 AAAA

102 02JAN2013:18:47 AAAA

102 02JAN2013 :18:47 AAAA

102 02JAN2013:23:08 AAAA

102 02JAN2013:23:59 BBBB

103 02JAN2013 :18:30 DDDD

104 02JAN2013 :18:30 EEEE

104 02JAN2013:18:47 EEEE

;;;;

run;

data want(drop=temp_count temp_code);

set have;

BY ID;

retain temp_count temp_code;

if(first.id) then do

temp_code=CODES;

temp_count=0;

output want;

end;

if(temp_count eq 0) then do;

if(codes ne temp_code) then do

temp_count=1;

output want;

end;

else

delete;

end;

run;