Help using Base SAS procedures

Help in filtering the data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Help in filtering the data

Hi guys, I just picked up SAS, and have trouble doing the following. There are around 100k of observations, hence its impossible for me to do it manually. As you can see, there are duplicates for one company (identified by the ISIN) due to the different statements that were produced by bankscope. There are 6 types of possible statements for a company in a certain year. And the priority should be C1/C2, C*, U1/U2,U* whenever possible as shown in table 2. In another words, the priority should be followed and duplicates to be deleted. Thanks in advance guys.

Table 1

yearISINConsolidation codetotal assets
2005AU123C*20
2005AU123C218
2006UK345C125
2006UK345C*40
2006UK345U128

Table 2

yearISINConsolidation codetotal assets
2005AU123C218
2006UK345C125

Accepted Solutions
Solution
‎05-26-2015 09:41 AM
Trusted Advisor
Posts: 1,130

Re: Help in filtering the data

Alternatively

data have;

input year$5.    ISIN$6. Consolidation_code$3.    total_assets$3.;

Consolidation_code=tranwrd(Consolidation_code,'*','99');

cards;

2005    AU123    C*    20

2005    AU123    C2    18

2006    UK345    C1    25

2006    UK345    C*    40

2006    UK345    U1    28

;

proc sort data=have;

by year isin Consolidation_code;

run;

data want;

set have;

by year isin Consolidation_code;

if first.isin;

run;

Thanks,

Jag

Thanks,
Jag

View solution in original post


All Replies
Contributor
Posts: 30

Re: Help in filtering the data

Hi ,

You can use the below approach .

data ranked ;

set have ;

if Consolidation_code  = "C1" then rank = 1 ;

else if  Consolidation_code = "C2" then rank = 2 ;

else if Consolidation_code = "C*" then rank =3 ;

else if Consolidation_code   = "U1" then rank = 4 ;

else if Consolidation_code  = "U2" then rank = 5 ;

else if Consolidation_code  = "U*" then rank = 6 ;

else rank = 9 ;

run;


proc sort data = ranked ;

by Consolidation_code rank ;

run;


data final ;

set ranked;

by Consolidation_code rank ;

if first.Consolidation_code ;

run;


thanks





Solution
‎05-26-2015 09:41 AM
Trusted Advisor
Posts: 1,130

Re: Help in filtering the data

Alternatively

data have;

input year$5.    ISIN$6. Consolidation_code$3.    total_assets$3.;

Consolidation_code=tranwrd(Consolidation_code,'*','99');

cards;

2005    AU123    C*    20

2005    AU123    C2    18

2006    UK345    C1    25

2006    UK345    C*    40

2006    UK345    U1    28

;

proc sort data=have;

by year isin Consolidation_code;

run;

data want;

set have;

by year isin Consolidation_code;

if first.isin;

run;

Thanks,

Jag

Thanks,
Jag
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 212 views
  • 3 likes
  • 3 in conversation