Help using Base SAS procedures

RE: Keeping deleted data (in seperate database)

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

RE: Keeping deleted data (in seperate database)

Hi all

First I would like to outline what I aim to do in sas commands:

I am looking to delete the permnos with previous reference up to 2 years. To ellaborate, if the permno (company identifier) has a distcd record of 1262 or 1272 in the same year, one year prior or two years prior. The most recent record must be deleted.

Consider the following examples (see the data below):

eg1: Permno 1005 has a record of distcd 1262 for 2000. However there is also a record for permno 1005 of distcd 1272 in 1999. The 2000 observation should be deleted.

eg2: Permno 1005 has a record of distcd 1262 for 2000. However there is also a record for permno 1005 of distcd 1262 in 1998. The 2000 observation should be deleted.

eg3: Permno 1005 has a record of distcd 1272 for DECEMBER 2000. However there is also a record for permno 1005 of distcd 1262 in APRIL 2000. The DECEMBER 2000 observation should be deleted.

Also, I would like to ask whether these deletions could be saved in a separate database?

PERMNODISTCDDIVAMTYEARYEARMONTHDCLRDT
1000212720.08199319931219931215
1000212720.25199719970319970325
1000612722195319530719530702
1000612721195419540819540802
1000612720.1196419641119641106
1000612720.2196519650219650205
1000612720.15196519650519650507
1000612720.15196519650819650806
1001412720.15193319331119331101
1001412720.15193419340219340201
1001412720.15193419340519340501
1001412720.15193419340819340801
1001412720.15193419341119341101
1001412720.15193519350219350201
1001412720.15193519350519350501
1001412720.15193519350819350801
1001412720.15193519351119351101
1001412720.15193619360219360201
1001412720.15193619360519360501
1001412720.15193619360819360801
1001412720.15193619361119361102
1001412720.15193719370219370201
1001412720.15193719370519370501
1001412720.15193719370819370802
1001412720.3193719371119371101
1001412720.15193819380219380201
1001412720.15193819380519380502
1001412720.15193819380819380801
1001412720.15193819381119381101
1001612720.2198819881119881121
1002212720.25192719271219271209
1002212720.25192819280319280312
1002212720.25192819280619280611
1002212720.25192819280919280910
1002212720.25192819281219281210
1002212720.25192919290319290308
1002212720.25192919290619290610
1002212720.25192919290919290910
1002212720.25193519350319350330
1002212720.25193619361219361219
1002212720.25194219421119421116
1002212720.25194719470119470115
1003012720.05193519350319350330
1003012720.25193519351219351231
1003012721193619361219361221
1003012720.25193919391219391221
1003012720.6194019401219401228
1003012720.1194119410619410630
1003012720.1194119410919410930
1003012720.4194119411219411227
1003012720.1194219420619420630
1003012720.1194219420919420930
1003012720.5194219421219421230
1003012720.15194319430619430630
1003012720.15194319430919430930
1003012720.5194319431219431228
1003012720.1194419440319440331
1003012720.15194419440619440630
1003012720.15194419440919440930
1003012720.25194419441219441228
1003012720.1194519450319450331
1003012720.15194519450619450630
1003012720.15194519450919450929
1003012720.25194519451219451228
1003012720.25194619460619460629
1003012621195019500919500929
1003012720.5195119511219511226
1003012720.5195619561219561228
1003012720.5195719571219571230
1005712720.25193619361119361120
1005712720.5194619461119461120
1005712720.5194719471119471125
1005712720.5195019501119501121
1005712720.5195119510819510823
1005712720.5195119511119511121
1005712720.5195219520819520821
1005712720.5195219521119521120
1005712720.5195319530519530521
1005712720.5195319531119531120
1005712720.5195319531119531120
1005712720.5195419540519540520
1005712720.5195419541119541123
1005712621195419541119541123
1005712720.5195519550519550524
1005712720.5195519550819550823
1005712721195519551119551123
1005712720.5195619560519560524
1005712720.5195619560819560823
1005712721195619561119561124
1005712721195619561119561124
1005712720.5195719570519570523
1005712720.5195719570819570822
1005712720.5195719571119571122
1005712721195719571119571122
1005712620.5195919591119591120
1005712720.5196019601119601121
1005712620.5196119611119611117
1005712621.25196219621119621123
1005712621.25196319631119631126
1005712621.75196419641119641124
1005712720.5196519650719650722
1005712621196519651119651123
1005712720.5196619660719660728
1005712620.8196619661119661121
1005712620.5196719671119671121
1005712620.15196819681119681127
1005812720.1197319730119730102
1006512620.77195019500919500922
1006512620.84195119510919510928
1006512620.7195219520919520926
1006512620.67195319530919530925
1006512620.66195419540919540925
1006512620.4195519551019551018
1006512620.47195619560919560927
1006512620.47195719570919570927
1006512620.22197019700119700107
1006512620.19197619760119760108
1006512620.1442197819780119780112
1006512620.23664197919790119790111
1006512620.26743198019800119800110
1006512620.35255198119810119810108
1006512620.37296198219820119820114
1006512620.37016198319830119830113
1006512620.2997198419840119840112
1006512620.24548198519850119850110
1006512620.24372198619860119860109
1006512620.23198719870119870108
1006512620.31198719871119871112
1006512620.14199819981119981119
1006512620.03200020001020001009
1006512720.01200720070120070111
1008112720.175192619260319260318
1008112720.175192619260619260618
1008112720.175192619260919260928
1008112720.075192619261219261217
1008112720.05192719270319270325
1008412720.25198919890719890712
1008512720.25200520050220050209
1010212721192619260919260930
1010212722192719270919270930
1010212721192819280919280928
1010212721.5192919290919290930
1010212721.5193019300919300930
1010212721.5193119310919310930
1010212720.75193319331019331016
1010212721.5193419341019341015
1010212721193519350719350715
1010212721.5193519351019351015
1010212720.5193619360719360715
1010212721193619361019361015
1010212720.25193719370119370115
1010212720.25193719370419370415
1010212720.75193719370719370715
1010212720.75193719371019371015
1010212720.25193819380119380115
1010212720.25193819381019381015
1010212720.5193919391019391016
1010212720.25194019400419400415
1010212720.25194019400719400715
1010212720.25194019401019401015
1010212720.25194119410119410115
1010212720.25194119410419410415
1010212720.25194119410719410715
1010212720.25194119411019411015
1010212720.25194219420119420115
1010212720.25194219420419420415
1010212720.25194219420719420715
1010212720.25194219421019421015
1010212720.25194319430119430115
1010212720.25194319430419430415
1010212720.25194319430719430715
1010212720.25194319431019431015
1010212720.25194419440119440115
1010212720.25194419440419440415
1010212720.25194419440719440710
1010212720.25194419441019441010
1010212720.25194519450119450110
1010212720.25194519450419450416
1010212720.25194519450719450716
1010212720.25194519451019451015
1010212720.25194619460119460115
1010212720.25194619460419460425
1010212720.25194619460719460725
1010712723200420041120041109
1011912720.0055200720070920070907
1012012720.65198619861119861119
1012012720.5198719871119871111
1012012720.45198819881119881109
1012012724.8198919891119891108
1012012720.6199019901119901108
1012212720.12199319931219931201
1012212720.08199419941119941130
1012212720.12199519951119951129
1012212720.15199619961119961127
1012212720.07199919991219991201
1012212720.04200020001120001129
1012712720.2199819981019981015
1012712720.1200020000320000309
1014512721.5193719371119371101
1014512723193919391219391220
1014512722194019401219401220
1014512722194119411219411220
1014512721194219421219421219
1014512722194619461219461220
1014512723194719471219471220
1014512723194819481219481220
1014512724194919491219491220
1014512721195019501219501220
1014512721195119511219511220
1014512720.6195219521219521212
1014512720.6195319531219531210
1014512720.15195419541219541210
1014612720.05197419741219741218
1014612720.1197719771219771222
1014612720.1197819781219781220
1014612720.15197919791219791219
1015312720.5193619361219361224
1015312720.25193919391219391222
1015312720.15194519451219451224
1015312720.4194919491219491223
1015312721195019501219501222
1015312720.25195919591219591223
1016112720.25193719370919370901
1016112720.25194419441219441201
1016112721194719471219471201
1016112721194819481119481130
1016112621194919491219491201
1016112621195019501219501201
1016112721195119511219511201
1016112620.5195219521219521201
1016112720.5195319531219531201
1016112720.65195419541219541201
1016112621195519551219551217
1016112720.6195619561219561201
1018812720.5194819480919480902
1018812720.25194919490419490415
1018812620.5196219621119621127
1018812620.5196319631119631120
1019612720.15195419541219541210
1019612720.15195519551219551209
1020712720.1715199119910819910823
1020712720.53199719971119971126
1020912720.25194119411219411227
1020912720.5194219420919420930
1020912720.8194319430919430930
1020912720.8194419440919440929
1020912720.8194519450919450927
1020912721194619460919460926
1020912721.5194719470919470925
1020912721.5194819480919480928
1020912721.5194919490919490929
1020912721.5195019500919500928
1020912721.5195119510919510927
1020912721.5195219520919520926
1020912721.5195319530919530925
1020912721.5195419540919540924
1020912721.5195519550919550923
1020912721.5195619560919560921
1020912721.5195719570919570920
1020912721.5195819580919580919
1021412720.5199719970419970408
1021712721192619261019261001
1021712721192719271219271212
1021712721192819281219281207
1021712721192919291219291210
1021712721193019301019301001
1021712721193119311219311210
1021712720.15193619361219361224
1021712720.2194519450119450102
1021712720.25194619460119460102
1021712720.7194719470119470102
1021712720.4194819480119480102
1021712720.6194919490119490103
1021712620.4194919491019491001
1021712720.1195619560119560103
1021712720.15195719570119570102
1022512722192919291119291108
1022512724193019300819300808
1022512721193119310219310210
1022512721193219320219320210
1022512720.25194319430319430301
1022512720.25194319431219431201
1022512720.25194519450319450301
1022512720.25194619460319460301
1022512720.5194719470319470301
1022512720.75194819480319480301
1022512721194919490319490301
1022512721195019500319500301
1022512721195119510319510302
1022512721195219520319520303
1022512721195319530319530302
1022512721195419540319540302
1022512721195519550319550301
1022512721195619560319560301
1022512721195719570319570301
1022512721195819580319580301
1022512721195919590319590302
1022512721196019600319600301
1023212720.2199219921119921123
1023312720.25194619461219461227
1023312720.2195519551219551220
1023312720.3195619561219561220
1023412720.2197919790619790604
1023912720.125199219921119921110
1023912720.1199419941119941102
1023912720.25200320031120031104
1023912720.4200420040220040203
1023912720.3200420040520040504
1023912720.05200420040820040803
1023912720.9200420041120041102
1023912720.15200520050220050201
1023912720.25200520050820050802
1023912720.15200520051120051101
1023912720.25200620060220060215
1023912721.25200620060520060502
1023912720.2200620061120061107
1023912720.2200720070220070206
1023912720.35200720070820070807
1023912720.1200720071120071107
1023912720.25201020100220100210
1024112723192619260119260129
1024112721192819281119281115
1024112721192919291119291115
1024112721193019301019301031
1024112721193119311119311102
1024112721193519350219350215
1024112721193619360219360215
1024112721193619361119361116
1024112721194819481119481115
1024112721194919491119491115
1024112722195019501119501115
1024112721195119511119511115
1024112720.15195419541119541115
1024112720.2197419741219741210
1025212720.09198619861119861113
1025212720.0667198719871119871119
1026612720.06198719870119870116
1026812722192619260119260108
1026812722192719270119270107
1026812720.5192819280119280106
1026812721192919290119290107
1026812721193019300119300107
1026812720.1195019500119500104
1026812720.25195619560119560104
1026812720.25195819580119580103
1026812720.1195919591019591002
1026812720.1196019600119600105
1026812720.1196019600419600407
1026812720.05196419641119641124
1027612721194819480119480102
1027612720.3195119510419510402
1027612721.45195219520319520331
1027612720.3195319530319530331
1027612720.25196019600319600331
1028412722193819380119380103
1029012720.15198719870219870225
1029012721.44201020101120101101
1029212720.25192919291219291212
1029212720.25193019300319300312
1029212720.25193019300619300612
1029212720.25193019300919300912
1029212720.25193019301219301212
1029212720.25193119310319310312
1029212720.25193119310619310612
1029212720.25193119310919310911
1029212720.25193119311219311211
1029212720.25193219320319320311
1029212720.25193219320619320610
1029212720.25193219320919320912
1029212720.25193219321219321212
1029212720.25193319330419330401
1029212720.25193319330619330612
1029212720.25193319331019331002
1029212720.25193419340119340102
1029212720.5193519350119350102
1029212720.5193519350719350701
1029212720.25193619360119360102
1029212720.25193619360419360401
1029212721.5193619361219361215
1029212721193719370619370615
1029212722193719371219371215
1029212720.5193819380619380615
1029212721193819381219381215
1029212721193919390619390615
1029212721.25193919390919390915
1029212721.25194019400619400615
1029212721.25194019401219401216
1029212721194119410619410616
1029212721194119411219411215
1029212720.5194219420619420615
1029212720.5194219421219421215
1029212720.5194319431219431215
1029212720.5194419440619440615
1029212720.5194419441219441215
1029212720.5194519450619450615
1029212720.5194519451219451215
1029212720.5194619460619460615
1029212720.5194619461219461214
1029212720.5194719470619470614
1029212720.5194719471219471215
1029212720.25194819480619480615
1029212720.25194819481219481210
1029212720.25194919490619490610
1029212720.25194919491219491210
1029212720.25195019500619500610
1029212720.25195019501219501209
1029212720.25195119510619510609
1029212720.25195119511219511210
1029212720.25195219520619520610
1029212720.25195219521219521210
1029212720.25195319530619530610
1029212720.25195319531219531210
1029212720.5195419540919540910
1029212720.25195519550619550610
1029212720.25195519551219551210
1029212720.25195619560619560611
1029212720.25195619561219561210
1029212720.5195719571219571210
1029212720.75195819581219581210
1029212720.4195919591219591210
1029212720.4196019601219601209
1029212720.5196119611219611211
1029412720.095201020101120101109
1029712720.03198719871119871112
1029712720.05198819881219881208
1029712720.05198919891119891117
1029712720.05199319931119931110
1030812720.21198619861219861209
1030812720.21198719871219871208
1030812720.05199519951119951114
1031312620.49195119510919510926
1031312620.4195219520919520923
1031312620.37195319530919530922
1031312620.36195419540919540922
1031312620.21195519551019551018
1031312620.26195619560919560924
1032112721192619260719260720
1032112720.5192619261019261020
1032112720.5192719270719270720
1032112720.5192719271019271020
1032112720.5192819280119280120
1032112720.5192819280419280420
1032112720.5192819280719280720
1032112720.5192819281019281019
1032112720.75194919491219491229
1032112720.5195019500919500922
1032112620.5195019501219501228
1032112720.6195919591219591230
1032112620.2196219621119621115
1032112720.15196319631119631118
1034012720.2198619861119861112
1034112720.05199819980319980326
1034112720.15199919990319990325
1036412722.25193619361119361130

Accepted Solutions
Solution
‎07-27-2012 01:22 PM
Contributor
Posts: 52

Re: RE: Keeping deleted data (in seperate database)

Posted in reply to spraynardz90

Okay, how about the following? You can drop index_year and test_yearmonth as you see fit.

data desired discarded;

   set raw;

   by permno;

   retain index_year;

  

   test_yearmonth = input(yearmonth, yymmn6.);

   if    first.permno or

         intck("year", index_year, test_yearmonth) > 2 then

      do;

         index_year = year(test_yearmonth);

         output desired;

      end;

   else

      output discarded;

run;

View solution in original post


All Replies
PROC Star
Posts: 7,474

Re: RE: Keeping deleted data (in seperate database)

Posted in reply to spraynardz90

Do you only want to delete one record per permno? e.g., for the following case, which record(s) should be deleted?

1000612722195319530719530702
1000612721195419540819540802
1000612720.1196419641119641106
1000612720.2196519650219650205
1000612720.15196519650519650507
1000612720.15196519650819650806
Contributor
Posts: 60

Re: RE: Keeping deleted data (in seperate database)

Hi

Sorry if my blurb above is confusing. Basically I want firms that don't have the distcd regularly. I want the firms that have distcd 1272 or 1262 rarely, not commonly. Therefore I imposed the 2 year restriction. I thought if I could find the firms that only have 1262 or 1272 once every two years or longer this is not common and would be very helpful.

If first, we could write a command to automatically delete any permno with multiple SDD paid in a single year this would be very helpful.

Then second, impose the original restriction about two years. In which case, Arthur 195307 would be the only observation included.

PROC Star
Posts: 7,474

Re: RE: Keeping deleted data (in seperate database)

Posted in reply to spraynardz90

Why wouldn't 196411 also be included?

PROC Star
Posts: 7,474

Re: RE: Keeping deleted data (in seperate database)

You didn't answer my question.  If 196411 should have been kept, then you might be able to get what you want with something like:

data want1 leftover;

  set have;

  if distcd IN (1262,1272) then output leftover;

  else output want1;

run;

data want2 deleted;

  set leftover;

  by permno;

  last_year=lag(year);

  if first.permno then output want2;

  else do;

    if year-last_year le 2 then output deleted;

    else output want2;

  end;

run;

data want;

  set want1 want2;

run;

proc sort data=want;

  by permno year;

run;

Contributor
Posts: 60

Re: RE: Keeping deleted data (in seperate database)

Hi Arthur I followed your method and seem to have inconsistent results.

I would like one database with all deletions.

And another database with firms with a 2 year or greater hiatus in the dist 1262 or 1272. So only the observations with the permnos that have 1262 or 1272 with a separation of 2 years or greater.

I realise some firms may have distcd 1262 and 1272  at regular intervals throughout the years and the question was raised whether to delete the regular observations to manipulate the 2 year hiatus. Preferably do not do this. But if there is still some confusion you can.

See the attachment with what I am getting. Please let me know if you would like a sample of my original data.


error2.JPGerror.JPG
Contributor
Posts: 60

Re: RE: Keeping deleted data (in seperate database)

I seem to have overlooked 196411, yes it should be included.

Contributor
Posts: 52

Re: RE: Keeping deleted data (in seperate database)

Posted in reply to spraynardz90

Did you try the code I gave you?

Contributor
Posts: 60

Re: RE: Keeping deleted data (in seperate database)

Giving it a go now tish

PROC Star
Posts: 1,322

Re: RE: Keeping deleted data (in seperate database)

Posted in reply to spraynardz90

Hi,

Not sure I understand your requirements completely, but something like this might help:

data want
     dropped
  ;
  set have;
  by PermNo Year;
  retain _Year _dropflag; 
  if first.PERMNO then do;
    _year=.;
    _dropflag=.;
  end;
  if distcd IN (1262,1272) then do ;
    if not missing(_year) then do ;
      _dropflag=((year-_year) le 2) ;
    end ;
    _year=year;
  end;
  if last.PermNo and _dropflag then output dropped;
  else output want;
run;
PROC Star
Posts: 1,322

Re: RE: Keeping deleted data (in seperate database)

Sorry, there was at least one obvious mistake in my sugggestion.  Something like this maybe:

data want
     dropped
  ;
  set have;
  by PermNo Year;
  retain _Year _dropflag; 
  if first.PERMNO then do;
    _year=.;
    _dropflag=.;
  end;
  if distcd IN (1262,1272) then do ;
    if (not missing(_year)) and (not _dropflag) then do ;   *added not _dropflag to avoid it being reset to 0 after it has been set to 1;
      _dropflag=((year-_year)le 2) ;
    end ;
    _year=year;
  end;
  if last.PermNo and _dropflag then output dropped;
  else output want;
run;
Contributor
Posts: 60

Re: RE: Keeping deleted data (in seperate database)

Thanks Quentin, to clarify looking to:

-delete any observations with for a permno with multiple 1262 and 1272 observed in a single year. (see Arthur's example)

-ensure that every permno in the database has a separation of 2 years or longer for each observation.

EG: Permno 1005 dist 1262 in 1999 and permno 1005 dist 1272 in 2000 = DELETED (the 2000 observation)

EG: Permno 1005 dist 1272 in 1999 and permno 1005 dist 1272 in 2002 =DESIRED. No deletions

Does your command satisfy the above conditions?

PROC Star
Posts: 1,322

Re: RE: Keeping deleted data (in seperate database)

Posted in reply to spraynardz90

Well, I'm still confused as to what the rules are for what you want.  Maybe post an example with ~20 records for ~3 or 4 permnos, and show what you would want for output.

In any case, both Art and I took similar appraoches, using some sort of look-back (mine with by-group processing, his with lagging).  I think playing with either might get you where you want.  And of course there is likely  SQL solution....

--Q.

Contributor
Posts: 52

Re: RE: Keeping deleted data (in seperate database)

Posted in reply to spraynardz90

This works, given my understanding of what you need. Note that I read yearmonth in as a SAS date value.

data raw;

   input

      permno

      distcd

      divamt : 7.5

      year

      yearmonth : yymmn6.

      dclrdt : yymmdd8.;

   format yearmonth monyy7. dclrdt date9.;

   cards;

;

run;

proc sort data=raw;

   by permno dclrdt;

run;

data desired discarded;

   set raw;

   by permno;

   retain index_year;

   if    first.permno then

      do;

         index_year = year(yearmonth);

         output desired;

      end;

   else if

         intck("year", index_year, yearmonth) > 2 then

      do;

         index_year = year(yearmonth);

         output desired;

      end;

   else

      output discarded;

run;

Contributor
Posts: 60

Re: RE: Keeping deleted data (in seperate database)

Hi Tish

I have my dates in numeric not sas date. This is intentional, required for some of my future analysis. Is it possible to tweak the code to account for numeric dates.

🔒 This topic is solved and locked.

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

Discussion stats
  • 19 replies
  • 519 views
  • 6 likes
  • 6 in conversation