topic Re: need help with this pls in SAS Procedures

need help with this pls

Tal — Wed, 25 Feb 2015 23:38:03 GMT

I have a dataset like this:

Fname lname id score

--------------------------

John smith. 100 a

John smith. 100 b

Tom. Fonda. 200 a

Tom. Fonda. 300 b

Mila. Fonda. 400 a

.....

If same ppl apear more then once but with different id i randomly select only one record of that person but if they appear multiple times with the same id then they all supposed to get selected. Also single records get selected too. How do i achieve this. Anyone please?

By same ppl i mean same first and last name

Re: need help with this pls

Jagadishkatam — Thu, 26 Feb 2015 03:54:28 GMT

Please try

data have;

input Fname$ lname$ id score$;

cards;

John smith. 100 a

John smith. 100 b

Tom. Fonda. 200 a

Tom. Fonda. 300 b

Mila. Fonda. 400 a

;

proc sort data=have;

by Fname lname id;

run;

data want(drop=seq) id(keep=Fname lname id seq);

set have;

by Fname lname id;

if first.id then seq=1;

else seq+1;

output want;

if last.id then output id;

run;

data all;

merge want id;

by Fname lname id;

if seq >=1;

run;

Thanks,

Jag

Re: need help with this pls

art297 — Thu, 26 Feb 2015 05:45:27 GMT

It's late, so I don't guarantee that the following meets all of your stated conditions. Besides, that's your job to make sure that code does what you want it to do.

However, to understand the following code, you may have to do some Google searches to understand how a double-dow works.

data have;

input Fname$ lname$ id score$;

cards;

John smith. 100 a

John smith. 100 b

Tom. Fonda. 200 a

Tom. Fonda. 300 b

Mila. Fonda. 400 a

;

proc sort data=have;

by Fname lname id;

run;

data want;

do until (last.lname);

set have;

by Fname lname id;

if first.lname then nameseq=1;

else nameseq+1;

if first.id then idseq=1;

else idseq+1;

end;

do until (last.lname);

set have;

by Fname lname id;

random=ranuni(0);

output;

end;

run;

proc sort data=want;

by Fname lname random;

run;

data want (drop=nameseq idseq random);

set want;

by Fname lname;

if nameseq ge 2 and idseq ge 2 then output;

else if nameseq eq 1 and idseq eq 1 then output;

else if first.lname then output;

run;

Re: need help with this pls

shubhayog — Thu, 26 Feb 2015 12:12:11 GMT

Hi Tal,

I would like to update the reply from Jagadishkatam,

Please try with following

Data have;

input FNAME$ LNAME$ ID SCORE$;

datalines;

John smith. 100 a

John smith. 100 b

Tom. Fonda. 200 a

Tom. Fonda. 300 b

Mila. Fonda. 400 a

Mila. smith. 500 a

Zen Fonda. 600 b

Tom. smith. 700 c

;

run;

proc sort

data=have;

by FNAME LNAME ID;

run;

data want;

set have;

by FNAME LNAME ID;

if first.ID then ISEQ=1;

else ISEQ+1;

if first.LNAME then LSEQ=1;

else LSEQ+1;

run;

data all (drop=ISEQ LSEQ);

merge want have;

by FNAME LNAME ID;

if ISEQ eq LSEQ;

run;

Hope it will work for you,

Please share if any abnormal observations.

Thank you Jagadishkatam for hint

Thanks and Regards,

Yogesh

Re: need help with this pls

RW9 — Thu, 26 Feb 2015 13:17:44 GMT

Hi,

Well, not sure about your logic of "i randomly select only one record", would need to be some kind of logical selection. In my example below I take the smallest for instance - first part gets records with all the same, second gets the different ones:

proc sql;

create table WANT as

select A.FNAME,

A.LNAME,

A.ID,

A.SCORE

from WORK.HAVE A

where not exists(select distinct FNAME from WORK.HAVE where FNAME=A.FNAME and LNAME=A.LNAME and ID ne A.ID)

union all

select FNAME,

LNAME,

ID,

SCORE

from (select * from WORK.HAVE B where exists(select distinct FNAME from WORK.HAVE where FNAME=B.FNAME and LNAME=B.LNAME and ID ne B.ID))

group by FNAME,LNAME

having SCORE=min(SCORE);

quit;

Re: need help with this pls

Tal — Tue, 03 Mar 2015 15:09:31 GMT

luckely i dont need to do this but thanks for the approaches guys .

will go over them just for practice