topic Re: Creating Daily Records in SAS Procedures

Creating Daily Records

deleted_user — Thu, 17 Mar 2011 00:34:21 GMT

I have data in the format:

ARMYID DATE RANK PAY
103164 2/7/2007 O-1 $8.20
103164 4/6/2008 O-3 $8.70
103164 9/17/2009 O-5 $10.25
475405 6/14/2007 E-4 $15.35
475405 9/28/2008 E-6 $18.40
475405 5/11/2009 E-8 $22.90
589071 9/9/2007 O-7 $14.10
589071 2/30/2008 O-9 $19.65
589071 4/19/2009 O-11 $28.80

I need to create daily records for each ARMYID from the date of enlistment to 12/31/2009 assuming that rank and pay remain constant between dates. I feel that with first and last variables, a do loop, and the output statement that I should be able to create these records however I can't seem as of now to write a code that will do this. Any help would be greatly appreciated. Thanks in advance.

Tyler

Re: Creating Daily Records

Ksharp — Thu, 17 Mar 2011 02:45:46 GMT

Hi.
What does your output look like?
Post is not very clarify.

Ksharp

Re: Creating Daily Records

Cynthia_sas — Thu, 17 Mar 2011 02:55:09 GMT

Hi:
I'm not sure what you mean when you say that you need to "create daily records" for each ARMYID. For example, you have this for ID 103164:
[pre]
ARMYID DATE RANK PAY
103164 2/7/2007 O-1 $8.20
103164 4/6/2008 O-3 $8.70
103164 9/17/2009 O-5 $10.25
[/pre]

And what I think you want to generate is one observation for each day that they held the O-1 rank, and then generate one observation for each day that they held the O-3 rank, etc. So that the "new" dataset looks like this (white space added for readability only):
[pre]
ARMYID DATE RANK PAY
103164 2/7/2007 O-1 $8.20
103164 2/8/2007 O-1 $8.20
103164 2/9/2007 O-1 $8.20
103164 2/10/2007 O-1 $8.20
103164 2/11/2007 O-1 $8.20
...more observations...
103164 4/5/2008 O-1 $8.20

103164 4/6/2008 O-3 $8.70
103164 4/7/2008 O-3 $8.70
103164 4/8/2008 O-3 $8.70
103164 4/9/2008 O-3 $8.70
...more observations...
103164 9/16/2009 O-3 $8.70

103164 9/17/2009 O-5 $10.25
... more observations...
103164 12/31/2009 O-5 $10.25
[/pre]

Can you clarify what your desired output should look like? Do you want a SAS dataset or do you just want a report of some kind that shows the total amount of money they made at each level??

cynthia

Re: Creating Daily Records

deleted_user — Thu, 17 Mar 2011 04:19:59 GMT

Hey Cynthia,

Sorry for the 'daily records' terminology, I should have been more specific. Your example of the 'new' dataset is exactly what I am looking for. Yes, I would like a SAS dataset. I currently have a SAS dataset with the data in the format of my original post. Thanks for your response.

Tyler

Re: Creating Daily Records

Ksharp — Thu, 17 Mar 2011 07:40:20 GMT

OK.
That is much more complicated than I image.

[pre]

data temp;
input ARMYID $ DATE : mmddyy10. RANK $ PAY : dollar10.;
format date mmddyy10. pay dollar10.2;
datalines;
103164 2/7/2007 O-1 $8.20
103164 4/6/2008 O-3 $8.70
103164 9/17/2009 O-5 $10.25
475405 6/14/2007 E-4 $15.35
475405 9/28/2008 E-6 $18.40
475405 5/11/2009 E-8 $22.90
589071 9/9/2007 O-7 $14.10
589071 2/28/2008 O-9 $19.65
589071 4/19/2009 O-11 $28.80
;
run;
[/pre]
proc sort data=temp;

by armyid date;

run;

data result;

set temp;

by armyid ;

start=lag(date);end=date;
_rank=lag(rank);_pay=lag(pay); r=rank;p=pay;

if first.armyid then output;

else do;

output;

do i=start+1 to end-1;

date= i;rank=_rank;pay=_pay;

output;

end;

if last.armyid and (end lt '31dec2009'd ) then do;

do j=end+1 to '31dec2009'd ;

date=j; rank=r;pay=p;

output;

end;

drop i j r p start end _: ;

run;

proc sort data=result;

by armyid date;

run;

[pre]

[/pre]

Ksharp

Re: Creating Daily Records

data_null__ — Thu, 17 Mar 2011 11:42:03 GMT

If you look ahead instead of back the problem is much easier.

[pre]
data days;
set temp end=eof;
by armyid;
if not eof then set temp(firstobs=2 keep=date rename=date=nxdate);
do date = date to ifn(last.armyid,'31dec2009'd,nxdate-1);
output;
end;
drop nxdate;
run;
proc compare base=result compare=days note;
run;

NOTE: No unequal values were found. All values compared are exactly equal.
NOTE: The data sets WORK.RESULT and WORK.DAYS are equal in all respects.
NOTE: There were 2836 observations read from the data set WORK.RESULT.
NOTE: There were 2836 observations read from the data set WORK.DAYS.
[/pre]

Re: Creating Daily Records

Ksharp — Fri, 18 Mar 2011 01:59:59 GMT

Hi. Great.
I do not even consider about the skill mentioned by Peter.C before.
But there is a problem . When last.armyid has a date greater than 2009-12-31, then the
result of you and me will have little different. Look below:

[pre]

[/pre]

datalines;

103164 2/7/2007
O-1 $8.20

103164 4/6/2008
O-3 $8.70

103164 1/2/2010
O-5 $10.25

475405 6/14/2007
E-4 $15.35

475405 9/28/2008
E-6 $18.40

475405 5/11/2009
E-8 $22.90

589071 9/9/2007
O-7 $14.10

589071 2/28/2008
O-9 $19.65

589071 4/19/2009
O-11 $28.80

NOTE: Data set
WORK.RESULT contains 1 observations not in WORK.DAYS.

NOTE: Values of the following 4 variables compare unequal: ARMYID DATE RANK
PAY

NOTE: The data sets WORK.RESULT and WORK.DAYS contain unequal values.

NOTE: There were 2838 observations read from the data set WORK.RESULT.

NOTE: There were 2837 observations read from the data set WORK.DAYS.

NOTE: PROCEDURE COMPARE used (Total process time):

real time

1.78 seconds

cpu time
0.10 seconds

[pre]

[/pre]

Ksharp

Re: Creating Daily Records

data_null__ — Fri, 18 Mar 2011 11:47:31 GMT

Oh dear, you've change the rules.

[pre]max('31dec2009'd,date)[/pre]

Re: Creating Daily Records

Ksharp — Sat, 19 Mar 2011 02:36:24 GMT

Hi.
I know.OP only want before '31dec2009'd date,
But Isn't keeping integrated obs from origin data better than deleting them?
Maybe in some case, there are some obs after '31dec2009'd date? You see?

Regards
Ksharp