DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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

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.


Accepted Solutions
Solution
‎11-25-2014 03:46 AM
Occasional Contributor
Posts: 9

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

Thank so much.. I reviewed the code, found that there is a logic below which is removing these records. thanks thanks.. thanks.

View solution in original post


All Replies
Super User
Posts: 11,343

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

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

Occasional Contributor
Posts: 9

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

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

Trusted Advisor
Posts: 1,230

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

proc sql;

create table want as

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

from have

group by comp,polNo,description;

quit;

Occasional Contributor
Posts: 9

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

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.

Super User
Posts: 19,869

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

proc sort data=have;

by comp polNo description pol_date;

run;

data want;

set have;

by comp polno description;

if last.description;

run;

Occasional Contributor
Posts: 9

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

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.

Super User
Posts: 19,869

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

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.

Occasional Contributor
Posts: 9

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

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.

Super User
Posts: 19,869

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

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.

Occasional Contributor
Posts: 9

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

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

Occasional Contributor
Posts: 9

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

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.

Super User
Super User
Posts: 7,993

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

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;

Occasional Contributor
Posts: 9

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

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

Super User
Posts: 19,869

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

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

Specifically in the outfile dataset

🔒 This topic is solved and locked.

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

Discussion stats
  • 17 replies
  • 468 views
  • 1 like
  • 6 in conversation