topic Re: help to create a new dataset from the exsisting one in SAS Programming

help to create a new dataset from the exsisting one

pk2012 — Tue, 12 Feb 2013 18:02:26 GMT

Hi All,

I need a help with a creation of a variable. I have the following information.

Company Date IC ACCT ID

A 01/01/2006 YES 100

A 01/05/2006 YES 100

A 01/10/2006 100

B 07/15/2006 YES 110

B 07/21/2006 YES 110

B 07/25/2006 110

B 07/30/2006 110

C 10/01/2006 115

C 10/21/2006 115

IC and ACCT are character variables. I want a dataset where for first.ID if IC = "YES" or ACCT ="YES" then select all the observations for that ID. In other words, I want my output to look as follows:

Company Date IC ACCT ID

A 01/01/2006 YES 100

A 01/05/2006 YES 100

A 01/10/2006 100

B 07/15/2006 YES 110

B 07/21/2006 YES 110

B 07/25/2006 110

B 07/30/2006 110

Can someone please provide me with the code?

Thank you in Advance

Re: help to create a new dataset from the exsisting one

Haikuo — Tue, 12 Feb 2013 18:47:54 GMT

Data step maybe more efficient, but syntax wise, Proc SQL is simpler:

data have;

input (Company Date IC ACCT ID) (:$20.);

cards;

A 01/01/2006 YES . 100

A 01/05/2006 YES . 100

A 01/10/2006 . . 100

B 07/15/2006 . YES 110

B 07/21/2006 . YES 110

B 07/25/2006 . . 110

B 07/30/2006 . . 110

C 10/01/2006 . . 115

C 10/21/2006 . . 115

;

proc sql;

create table want as

select * from have

group by company

having sum(ic="YES")>0 or sum(acct='YES')>0

;

quit;

UPdate: Please ignore this reply. it is faulty. The one from DBailey is the correct one.

Haikuo

Re: help to create a new dataset from the exsisting one

DBailey — Tue, 12 Feb 2013 18:48:45 GMT

presuming your data is sorted by id

data want;

set have;

by id;

retain output_YN='N';

if first.id and (ic='YES' or ACCT='YES' then Output_YN='Y';

else if first.id then output_yn='N';

if Output_YN+'Y' then output;

run;

Re: help to create a new dataset from the exsisting one

Linlin — Tue, 12 Feb 2013 19:16:00 GMT

Borrowed Haikuo's data:

data have;

input (Company Date IC ACCT ID) (:$20.);

cards;

A 01/01/2006 YES . 100

A 01/05/2006 YES . 100

A 01/10/2006 . . 100

B 07/15/2006 . YES 110

B 07/21/2006 . YES 110

B 07/25/2006 . . 110

B 07/30/2006 . . 110

C 10/01/2006 . . 115

C 10/21/2006 . . 115

;

data want;

set have;

by company notsorted;

if first.company then group=0;

group+(ic='YES' or acct='YES') ;

if group>0;

proc print;run;

Re: help to create a new dataset from the exsisting one

hdodson_pacificmetrics_com — Tue, 12 Feb 2013 19:22:26 GMT

Hey there,

I have an SQL approach if you're so inclined:

data data_have;

infile datalines dsd dlm="," missover;

input Company : $char1.

Date : MMDDYY10.

IC : $char4.

ACCT : $char4.

ID : 8.

;

datalines;

A,01/01/2006,YES,,100

A,01/05/2006,YES,,100

A,01/10/2006,,,100

B,07/15/2006,,YES,110

B,07/21/2006,,YES,110

B,07/25/2006,,,110

B,07/30/2006,,,110

C,10/01/2006,,,115

C,10/21/2006,,,115

;

run;

proc sql;

create table data_want as

select a.*

from data_have as a,

(select distinct Company

from data_have

where IC='YES' or ACCT='YES') as tmp

where a.Company = tmp.Company;

quit;

Thanks,

Huey