can we get below output with first. and last. or groupformat

[veeru]

Input  file:
comp   polNo     description   pol-date
76536 002745782 Emp Basic AD&D 20100101
76536 002745782 Emp Basic AD&D 20091001
76536 002745782 Emp Basic AD&D 20081001
76536 003624352 Emp Basic AD&D 20050101
76536  003624352 Emp Optnl AD&D 20050101
76536  003728839 Emp Basic AD&D 20060701
76536  004600598 Emp Basic AD&D 20091001

Output
comp   polNo     description   pol-date
76536 002745782 Emp Basic AD&D 20100101 
76536 003624352 Emp Basic AD&D 20050101
76536  003624352 Emp Optnl AD&D 20050101
76536  003728839 Emp Basic AD&D 20060701
76536  004600598 Emp Basic AD&D 20091001

Outfile should be generated with Latest policy date , If compcode, polNo and pol-date are same but the Description is different then I need to that to be in output file (row 2 and 3). Please let me know sas code.

[ballardw]

Is this data a SAS data set? Since pol-date isn't a normal SAS variable name I thought it might be worth asking.

[veeru]

Yes, Its a SAS Dataset and Variable name is Pol_Date, Thank you inadvance for your support.

[stat_sas]

proc sql;

create table want as

select comp,polNo,description,max(pol_date) as pol_date

from have

group by comp,polNo,description;

quit;

[veeru]

Sorry @stat@sas, It is not giving expected results, results are like input file only.

Note: Apart comp,polno,description and Pol_date fields, there some other fields in that file, I have not listed out them in example, they are coming as blank when I try to print them thru PUT statement.

[Reeza]

proc sort data=have;

by comp polNo description pol_date;

run;

data want;

set have;

by comp polno description;

if last.description;

run;

[veeru]

Thank you, it is working partially, but not able handle if "description" are more than two types, below is example

Input :

76536 005666666 Dep Basic AD&D 20050101

76536 005666666 Dep optnl AD&D 20050101

76536 005666666 Emp Basic AD&D 20050101

76536 005666666 Emp Optnl AD&D 20050101

In this case, Output file should contain all these records

76536 005666666 Dep Basic AD&D 20050101

76536 005666666 Dep optnl AD&D 20050101

76536 005666666 Emp Basic AD&D 20050101

76536 005666666 Emp Optnl AD&D 20050101

Please do the needful.

[Reeza]

You'd have to provide more sample data.

From what I can see, it should still work and 1 record for each description, the one with the latest date should be outputted.

Or you need to explain your requirements more.

You can try playing around with the first. last. and your BY group variables to see if a different combination gives you what you want.

[veeru]

Input  file:
comp   polNo     description   pol-date
76536 002745782 Emp Basic AD&D 20100101
76536 002745782 Emp Basic AD&D 20091001
76536 002745782 Emp Basic AD&D 20081001
76536 003624352 Emp Basic AD&D 20050101
76536  003624352 Emp Optnl AD&D 20050101
76536  003728839 Emp Basic AD&D 20060701
76536  004600598 Emp Basic AD&D 20091001
76536 005666666 Dep Basic AD&D 20050101
76536 005666666 Dep optnl AD&D 20050101
76536 005666666 Emp Basic AD&D 20050101
76536 005666666 Emp Optnl AD&D 20050101

Output
comp   polNo     description   pol-date
76536 002745782 Emp Basic AD&D 20100101 
76536 003624352 Emp Basic AD&D 20050101
76536  003624352 Emp Optnl AD&D 20050101
76536  003728839 Emp Basic AD&D 20060701
76536  004600598 Emp Basic AD&D 20091001
76536 005666666 Dep Basic AD&D 20050101
76536 005666666 Dep optnl AD&D 20050101
76536 005666666 Emp Basic AD&D 20050101
76536 005666666 Emp Optnl AD&D 20050101

Tried with different options, not able to get expected output, Pls do the needful.

[Reeza]

If the code didn't work, show a detailed example of your input, code and the output YOU get.

From everything we can see it should work, so there's something we're not seeing.

[veeru]

My Requirement is : output should have latest policy details

fields are: compcode, pol no, pol description and pol date, some to other fields

Following Code I have written

input file gets created in previous steps

PROC SORT DATA=inputfile OUT=WORK.TEMP;              

BY MCE_COMPANY_CODE MCE_POLICY_NUMBER MCE_PLAN_CODE_DESC  

     MCE_POLICY_DATE;         

RUN;                                                        

DATA WORK.outputfile;                              

SET WORK.TEMP;                                            

BY MCE_COMPANY_CODE MCE_POLICY_NUMBER MCE_PLAN_CODE_DESC;  

    IF LAST.MCE_PLAN_CODE_DESC;                            

RUN;  

Issues with below record:

input file data
346703982|Dep Optional AD&D|N/A|20100101|
346703982|Emp Basic AD&D|N/A|20050201|INA
346703982|Emp Optional AD&D|N/A|20050301|

output file data (it is suppose print DEP optional record along with below records, but it is not printing it. Like wise it is ignoring some records).
346703982|Emp Basic AD&D|N/A|20050201|IN
346703982|Emp Optional AD&D|N/A|20050301

[veeru]

The "LAST." is behaving different for the same kind of data. Need experts advice on this.

Examples 1:

---

code is working fine for below data
064421449|Emp Basic AD&D|N/A|20050101|INA
064421449|Emp Optional AD&D|N/A|20130201|
064421449|Dep Optional AD&D|N/A|20130201|
output file:
064421449|Emp Basic AD&D|N/A|20050101|INA
064421449|Emp Optional AD&D|N/A|20130201|
064421449|Dep Optional AD&D|N/A|20130201|

-----

Another example

Input data,this policy data same like above,
346703982|Emp Basic AD&D|N/A|20050201|INA
346703982|Emp Optional AD&D|N/A|20050301|
346703982|Dep Optional AD&D|N/A|20100101|

Outfile data:
it is writing only two records, It should write "Dep Optional" record as well.
346703982|Emp Basic AD&D|N/A|20050201|IN
346703982|Emp Optional AD&D|N/A|20050301

  -----

Below is the code for reference.

PROC SORT DATA=inputfile OUT=WORK.TEMP;              

BY MCE_COMPANY_CODE MCE_POLICY_NUMBER MCE_PLAN_CODE_DESC  

     MCE_POLICY_DATE;         

RUN;                                                        

DATA WORK.outputfile;                              

SET WORK.TEMP;                                            

BY MCE_COMPANY_CODE MCE_POLICY_NUMBER MCE_PLAN_CODE_DESC;  

    IF LAST.MCE_PLAN_CODE_DESC;                            

RUN;  

Quick help is highly appreciated.

[RW9]

Hi,

I am afraid you will need to clarify your post.  Post the code, actual code, with some test data (in a datastep) where this is happening.  For instance you mention it is writing only two lines, writing what, where?  no indication of how this is being written.  The following code works fine (flags all three recods), so there must be something else happening.  Run through your code step by step, replacing the if last then output with a flag so you can see in each step what is happening.  Identify exactly the datastep that is causing you problems.  Then look at the data for those rows.

data have;

  infile datalines dlm="|" dsd;

  input MCE_COMPANY_CODE MCE_POLICY_NUMBER $ MCE_PLAN_CODE_DESC $ A B $;

datalines;

346703982|Emp Basic AD&D|N/A|20050201|INA

346703982|Emp Optional AD&D|N/A|20050301|

346703982|Dep Optional AD&D|N/A|20100101|

run;

proc sort data=have; 

  by mce_company_code mce_policy_number mce_plan_code_desc;

run;

data want;

  set have;

  by mce_company_code mce_policy_number mce_plan_code_desc;

  if last.mce_plan_code_desc then flag="Y";

run;

[veeru]

Sorry for the confusion. there are million records in input file. for the same kind of data, it is (Last.)returning different results.(like above). Using the below statement to print the output file.

set output

FILE FINALOP DLM='|' DSD;          

IF _N_ = 1 THEN DO;                

  PUT 'MCE_COMPANY_CODE|'          

      'MCE_CONTRACT_SITUS_STATE|'  ,, remaining fileds.

-----

Pls advice

[Reeza]

Check the output file before the print, does it have the records?

Specifically in the outfile dataset