just the two syntax

[deleted_user]

hello all... I have value as follow

type | seq | id

900 | 1 | 12345
905 | 2 | 12345
166 | 3 | 12345
900 | 1 | 90171
905 | 2 | 90171
900 | 1 | 11145
905 | 2 | 11145
105 | 3 | 11145

what is my syntax should I want to output id for the asm_type only in 900, and 905 so in this case, the syntax would output 90171 only and count it as 1.

any help on this is greatly appreciated

thanks
regards...
neobita

[LinusH]

I'm not exactly clear how you want your output (best is to show an example output table as well).
Maybe PROC SORT NODUPKEY/PROC SQL Select Distinct with a WHERE-clause will do your job...?

/Linus

[deleted_user]

thank you for seeing my topic...

my output would be in a html format report... all i want is a select statement that can output a condition of asm_type = '900' and asm_type = '905' and not anything else.... even-though an id contains the '900' and '905' as its first two asm type, and if it has a third asm type, I would neglect the id all together... I am very new at sas, I hope my explanation is good enuf...

regards

[Flip]

data one;
input type seq id;
cards;
900 1 12345
905 2 12345
166 3 12345
900 1 90171
905 2 90171
900 1 11145
905 2 11145
105 3 11145
run;

proc sql;
create table two as select distinct id, type from one where id not in (select id from one where
seq GE 3);
quit;

[deleted_user]

it seems that I'm still unable to get the result I wanted. I want to output the ID with ASSET 900 for SEQ 1 and ID with ASSET 905 for SEQ 2 as a single ID and without a third SEQ

inside the database, it shows the following data.

ID----------------YEAR-----------TYP----------ASSET--------SEQ
76039803------2006--------------1---------------900-------------1
76039803------2006--------------1---------------905-------------2
77318706------2006--------------1---------------900-------------1
77318706------2006--------------1---------------905-------------2
86856104------2006--------------1---------------900-------------1
86856104------2006--------------1---------------905-------------2
88458302------2006--------------1---------------900-------------1
88458302------2006--------------1---------------905-------------2
200180480-----2006--------------1---------------900-------------1
200180480-----2006--------------1---------------905-------------2
276493707-----2006--------------1---------------900-------------1
276493707-----2006--------------1---------------905-------------2
687509501-----2006--------------1---------------105-------------3
687509501-----2006--------------1---------------900-------------1
687509501-----2006--------------1---------------905-------------2
123123123-----2006--------------1---------------900-------------0
123123123-----2006--------------1---------------905-------------1
88888888------2006--------------1---------------900-------------1
88888888------2006--------------1---------------123-------------2
99999999------2006--------------1---------------111-------------1
99999999------2006--------------1---------------905-------------2


The id that I want would be 076039803, 077318706 , 086856104 , 088458302 , 2001804807 and 276493707.

I Don't want the following ID
687509501 - because it contains more than 2 seq
123123123 - because it starts with seq 0
88888888 - because the second sequence ASSET is 123
99999999 - because the first sequence ASSET is 111

kindly share some light to this humble programmer.... any help given is truly appreciated,

regards
neobita

[DanielSantos]

Hi.

Try this:
[pre]
* ensure data is sorted;
proc sort data=INDATA;
by ID;
run;

data OUTDATA;
set INDATA;
by ID;
retain _GOOD; * flag;
keep ID;
if first.ID then _GOOD=1; * assume its a good ID;
if SEQ not in (1,2) or ASSET not in (900,905) then _GOOD=0; * fails;
if last.ID and _GOOD; * output last obs if good ID;
run;
[/pre]

Works with your sample, but please do some more intensive tests.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[Peter_C]

[pre]data two ;
do row=1 by 1 until( last.id ) ;
set origs ;
by id notsorted ;
if asset = '900' and seq=1 then f1='1' ;
if asset = '905' and seq=2 then f2='1' ;
else f3= '1' ;
end ;
if f3 ne '1' and f1!!f2 = '11' then output ;
run;[/pre]

[abdullala]

data one;
input type seq id;
cards;
900 1 12345
905 2 12345
166 3 12345
900 1 90171
905 2 90171
900 1 11145
905 2 11145
105 3 11145
run;


proc sql;
create table justtwo as
select distinct id from one
group by id
having count(id) =2 ;
quit;