how to retrieve records which contatin same value

[Ram4sas]

Hi,

I have below data

Account	Type	Salary
345	1	100
349	1	200
435	1	500
654	1	9000
654	2	6000
654		3000
987	1	15000
987	2	9000
987		6000
876	1	400
456	1	200

from above data, i want extract into new table only account records which Account having type in(1,2) with third row which show difference for salary amount.

i want extract account details which contain type in(1, 2) and third row for salary difference, don't want account which contain only type =1

Account	Type	Salary
654	1	9000
654	2	6000
654		3000
987	1	15000
987	2	9000
987		6000

i have tried to extract with multiple trials, but not getting required output.

i tired with

data want;

set have;

retain account;

where type in (1,2,.);

run;

which gives all records.

and tried with this code

proc sort data=have;

by Account type;

run;

data want;

set have;

by account type;

if first.account and last.account then output;

run;

this code result giveing result output all five records except required 6 records for account 654(3 Records) and 987(3 records).

can you suggest how to get the requried output from having data?

Thank you very much

[art297]

I think that the following will extract the records that you want:

proc sort data=have;

  by Account;

run;

data want (drop=t t1 t2 t3);

  do until (last.Account);

    set have;

    by Account;

    if type eq 1 then t1=1;

    else if type eq 2 then t2=1;

    else if missing(type) then t3=1;

    if last.Account then if t1 and t2 and t3 then t=1;

  end;

  do until (last.Account);

    set have;

    by Account;

    if t then output;

  end;

run;

[Ram4sas]

HI Arthur,

thank you for reply and output coming like

Account	Type	Salary
654		3000
654	1	9000
654	2	6000
987		6000
987	1	15000
987	2	9000

can i populate type eq missing below to to record of type eq 2 as the type eq missing record carry difference value for type 1-2(mean 9000-6000=3000.

i mean i am looking for to generate output like below

Account	Type	Salary
654	1	9000
654	2	6000
654		3000
987	1	15000
987	2	9000
987		6000

thank you very much for your reply .

[art297]

The code I suggested would not have changed the order of the type=missing records, thus your data was either not arranged as shown in your example or you added type to proc sort.  Apparently it was the former and, in that case, using KSharp's suggested proc sql code will provide your best solution.

[Ksharp]

Suppose there are only 1,2,. three value in type variable.

data have;
input Account     Type     Salary;
cards;
345     1     100
349     1     200
435     1     500
654     1     9000
654     2     6000
654     .     3000
987     1     15000
987     2     9000
987     .     6000
876     1     400
456     1     200
;
run;
proc sql;
 create table want as 
  select * from have 
   group by Account
    having sum(type=1)=1 and sum(type=2)=1 and sum(type=.)=1
     order by Account,missing(type);
quit;

Xia Keshan

Message was edited by: xia keshan

[Ram4sas]

Hi Ksharp,

thank you for reply, the output coming like this with your code

Account	Type	Salary
654	2	6000
654	1	9000
654		3000
987	2	9000
987	1	15000
987		6000

but the type value should populate type 1 in first record, then 2 in second record and finally missing as its carry the difference value for type 1-2(9000-6000=3000).

can i get output like this

Account	Type	Salary
654	1	9000
654	2	6000
654		3000
987	1	15000
987	2	9000
987		6000

[Ksharp]

Sure.

proc sql;

create table want as

  select * from have

   group by Account

    having sum(type=1)=1 and sum(type=2)=1 and sum(type=.)=1

     order by Account,missing(type),Type;

quit;

Xia Keshan

[Ram4sas]

Hi Ksharp,

Thank you once again..

I tried this code, its working for above sample data.

but when i tried to do create table with my actual data where Account and Type columns are string not number.

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

proc sql;

create table want as

  select Account, Type from have

   group by Account

    having sum(type='1')=1 and sum(type='2')=1 and sum(type=' ')=1

     order by Account,missing(type),Type;

quit;

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

its giving 0 records in output.

in log it says that :

The query as specified involves ordering by an item that doesn't appear in its SELECT Clause.

The query requires remerging summary statistics back with the original data.

why its not working for this case..

[Ram4sas]

Hi Ksharp,

Its working fine.. thank you very much once again

[Ram4sas]

HI Ksharp,

I got stuck up with one issue here. the above code is working fine for records where the account has type 1 and 2 values from date.

we are giving output based on record_gen dates users select date on which date user wanna see account details.

the account and type values being populating on different dates like for account 001, type =1 is available on 01JUN2014 record_gen date and account 001 and type=2 is avalaible on 25JUN2014 record_gen Date.

when user select 25JUN2014 date to see the account records, no records are getting retrived, because for account 001& type=2 is there for 25JUN2014.

account 001 and type=1 record availbel for previous date 01JUN2014.

How to get the both records for same account record contain type values in different dates?

thanks...

[Ksharp]

OK.You said you output data based on record_gen dates , But you didn't post your sample data . I just describe data like this :

data have;
input Account     Type     Salary  record_gen : date9. ;
format record_gen date9.;
cards;
345     1     100 25JUN2014
349     1     200 25JUN2014
435     1     500  25JUN2014
654     1     9000   01JUN2014
654     2     6000   25JUN2014 
654     .     3000   01JUN2014
987     1     15000     25JUN2014
987     2     9000 25JUN2014
987     .     6000  25JUN2014
876     1     400  01JUN2014
456     1     200  01JUN2014
;
run;

%let date= 25JUN2014 ;
proc sql;
create table want as
select * from have 
 where Account in (
  select distinct Account from have where      record_gen="&date"d

  intersect

  select distinct Account from have
   group by Account
    having sum(type=1)=1 and sum(type=2)=1 and sum(type=.)=1

)
     order by Account,missing(type),Type;
quit;

Xia Keshan

[Ram4sas]

Hi,

data is below for your information

Rec_Gen_Dt      Account      Type      salary

01JUN2014        001               01          230    

01JUN2014        002               01          678   

01JUN2014        003               01          980

25JUN2014        001               02          234

25JUN2014        002               02          670

25JUN2014        003               02          980    

we set up stored process code with dynamic prompt to provide access to user to select record_gen_dt values.

when user select date prompt value 25JUN2014, below report should come.

Account      Type      salary

001               01          230 

001               02          234 

002               01          678

002               02          670

003               01          980

003               02          980

I hope this would help to uinderstand my query.

thank you in advace.

[Ksharp]

OK. My code should help you a little bit .

data have;
input Rec_Gen_Dt   : date9.   Account      Type      salary     ;
format Rec_Gen_Dt date9.;
cards;
01JUN2014        001               01          230    
01JUN2014        002               01          678   
01JUN2014        003               01          980
25JUN2014        001               02          234
25JUN2014        002               02          670
25JUN2014        003               02          980    
;
run;




%let date= 25JUN2014 ;
proc sql;
select * from have 
 where Account in (
  select distinct Account from have where      Rec_Gen_Dt="&date"d

  intersect

  select distinct Account from have
   group by Account
    having sum(type=1)=1 and sum(type=2)=1 

)
     order by Account,missing(type),Type;
quit;

Xia Keshan

[Ram4sas]

HI Keshan,

the above code works for 25JUN2014 date prompt as the accounts got salary changed on 25JUN2014 (as excepted to retrieve account details which got changed their Salary in last 25 days 01JUN2014).

But when I select 01JUN2014 value in prompt, it gives out put for account numbers with records type=2 which actually got generate/type changed on 20JUN2014 or 25JUN2014, these records supposed to not to retrieve for 01JUN2014 prompt values, and 01Jun2014 date prompt it has to retrieve records from last month 05APR2014 if records available else no output.

I want to restrict to not show 20/25JUN2014 record_gen_date records (type=2) when I select 01JUN2014 and run the code.

Basic query is when I select date prompt, it should full account records for last 25 days with type=1 & 2, not for future dates records.

I hope I had placed query correctly. reply text if query not cache.

thank you very much.

[Ksharp]

You only want query obs last 25 days with type=1 & 2  ? what if one of an Account's obs(either type=1 or type=2) is out of this range ,this Account will not be printed either ? if so .

data have;
input Rec_Gen_Dt   : date9.   Account      Type      salary     ;
format Rec_Gen_Dt date9.;
cards;
01JUN2014        001               01          230    
01JUN2014        002               01          678   
01JUN2014        003               01          980
25JUN2014        001               02          234
25JUN2014        002               02          670
25JUN2014        003               02          980    
;
run;




%let date= 01JUN2014  ;
proc sql;
select * from have 
 where Account in (
  select distinct Account from have where      Rec_Gen_Dt="&date"d

  intersect

  select distinct Account from have
   where Rec_Gen_Dt between "&date"d-25  and "&date"d
    group by Account
     having sum(type=1)=1 and sum(type=2)=1 

)
     order by Account,missing(type),Type;
quit;

Xia Keshan