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.
Thank so much.. I reviewed the code, found that there is a logic below which is removing these records. thanks thanks.. thanks.
Is this data a SAS data set? Since pol-date isn't a normal SAS variable name I thought it might be worth asking.
Yes, Its a SAS Dataset and Variable name is Pol_Date, Thank you inadvance for your support.
proc sql;
create table want as
select comp,polNo,description,max(pol_date) as pol_date
from have
group by comp,polNo,description;
quit;
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.
proc sort data=have;
by comp polNo description pol_date;
run;
data want;
set have;
by comp polno description;
if last.description;
run;
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.
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.
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.
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.
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
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.
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;
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
Check the output file before the print, does it have the records?
Specifically in the outfile dataset
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.