BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
8 REPLIES 8
Ksharp
Super User
Hi.
What does your output look like?
Post is not very clarify.



Ksharp
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Ksharp
Super 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

data_null__
Jade | Level 19
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]
Ksharp
Super User
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
data_null__
Jade | Level 19
Oh dear, you've change the rules.

[pre]max('31dec2009'd,date)[/pre]
Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1304 views
  • 0 likes
  • 4 in conversation