topic Re: can we get below output with first. and last. or groupformat in SAS Programming

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

veeru — Thu, 30 Oct 2014 19:10:59 GMT

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.

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

ballardw — Thu, 30 Oct 2014 19:25:21 GMT

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

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

veeru — Thu, 30 Oct 2014 19:33:32 GMT

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

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

stat_sas — Thu, 30 Oct 2014 19:46:56 GMT

proc sql;

create table want as

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

from have

group by comp,polNo,description;

quit;

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

veeru — Thu, 30 Oct 2014 20:42:36 GMT

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.

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

Reeza — Thu, 30 Oct 2014 21:14:18 GMT

proc sort data=have;

by comp polNo description pol_date;

run;

data want;

set have;

by comp polno description;

if last.description;

run;

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

veeru — Fri, 31 Oct 2014 05:55:42 GMT

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.

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

Reeza — Fri, 31 Oct 2014 11:51:27 GMT

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.

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

Kurt_Bremser — Fri, 31 Oct 2014 11:58:49 GMT

That is exactly what Reeza's code will yield. Test it:

data have;

infile cards dlm=',';

length

comp $5

polno $9

description $20

;

informat pol_date yymmdd8.;

input

comp polno description pol_date

;

cards;

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

;

run;

proc sort data=have;

by comp polNo description pol_date;

run;

data want;

set have;

by comp polno description;

if last.description;

run;

Result:

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

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

veeru — Mon, 24 Nov 2014 06:59:58 GMT

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.

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

Reeza — Mon, 24 Nov 2014 09:56:43 GMT

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.

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

veeru — Mon, 24 Nov 2014 11:23:17 GMT

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

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

veeru — Mon, 24 Nov 2014 14:18:47 GMT

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.

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

RW9 — Mon, 24 Nov 2014 14:40:19 GMT

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;

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

veeru — Mon, 24 Nov 2014 15:36:37 GMT

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

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

Reeza — Tue, 25 Nov 2014 03:15:48 GMT

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

Specifically in the outfile dataset

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

veeru — Tue, 25 Nov 2014 08:46:11 GMT

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

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

Reeza — Tue, 25 Nov 2014 08:49:44 GMT

Glad you found your answer, but you really shouldn't mark yourself as providing the correct answer.

Several people provided the correct answer and hints.