Help using Base SAS procedures

Creating Daily Records

Reply
N/A
Posts: 0

Creating Daily Records

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
Super User
Posts: 10,041

Re: Creating Daily Records

Posted in reply to deleted_user
Hi.
What does your output look like?
Post is not very clarify.



Ksharp
SAS Super FREQ
Posts: 8,868

Re: Creating Daily Records

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Creating Daily Records

Posted in reply to Cynthia_sas
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
Super User
Posts: 10,041

Re: Creating Daily Records

Posted in reply to deleted_user
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;



        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;



                                                 
end;



 drop i j r p start end _: ;



run;



proc sort data=result;



 by armyid date;



run;



 


[pre]





[/pre]



Ksharp

Respected Advisor
Posts: 3,799

Re: Creating Daily Records

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]
Super User
Posts: 10,041

Re: Creating Daily Records

Posted in reply to data_null__
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
Respected Advisor
Posts: 3,799

Re: Creating Daily Records

Oh dear, you've change the rules.

[pre]max('31dec2009'd,date)[/pre]
Super User
Posts: 10,041

Re: Creating Daily Records

Posted in reply to data_null__
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
Ask a Question
Discussion stats
  • 8 replies
  • 393 views
  • 0 likes
  • 4 in conversation