Friends,
I am new to SAS programming.
I have a task to create the following report from ps-ef output on AIX. Each hour how many new Zombies created, died and remaining.
Server_Name Date Time +NewZombies -ZombiesDied TotalZombies
Srv1 Oct1 00:00 7 3 4
Srv2 Oct1 01:00 2 3 3 (4 from prior hour + 2 new – 3 died = 3 remaining)
This is how my data looks like:
Server_Name PID Date Proc_Start_hour Proc_End_hour
Srv1 1 Oct1 0 9
Srv1 2 Oct1 1 2
Srv1 3 Oct1 2 23
Srv1 4 Oct1 4 4
Please give me logic/ideas to create the above report with the data i have. Really appriciate any help here.
Thanks
Gopal
correction:
with the sample data i want my final report to looks like this.
Server_Name Date Time +NewZombies -ZombiesDied TotalZombies
Srv1 Oct1 00:00 1 0 1
Srv2 Oct1 01:00 1 0 2 (1 from prior hour + 1 new – 0 died = 2 remaining)
Srv2 Oct1 02:00 1 1 2 (2 from prior hour + 1 new – 1 died = 2 remaining)
Srv2 Oct1 03:00 1 0 3 (2 from prior hour + 1 new – 0 died = 3 remaining)
............................
............................
.............................
It looks like something like this is a start:
data want;
set have;
retain totalzombies 0;
totalzombies = sum(totalz, newzombies, -1*zombiesDied);
run;
I used sum in case any of your newzombies or zombiesdied variables are missing values.
Thank you,
but in my data Zombies died variable is not htere and i have to track the PID and subtract it in the hour it died.
I think it would help if you could clarify your example and requirements a little more.
Thank you for your feedback/questions. Sorry for the typos. as i can not post the original data, i typed in a hurry. To start with we are analyzing data within a day/date. If process runs for more than a day then we ignore that process for now. Basically this report is only for a single date.
following is the corrected report format and data:
This is how my data looks like:
Server PID Date Proc_Start_hour Proc_End_hour
Srv1 1 Oct1 0 9
Srv1 2 Oct1 1 2
Srv1 3 Oct1 2 23
Srv1 4 Oct1 3 11
Srv1 5 Oct1 4 4
Srv1 6 Oct1 5 22
This is the expected report format:
Server Date Time +NewZombies -ZombiesDied TotalZombies
Srv1 Oct1 00:00 1 0 1
Srv1 Oct1 01:00 1 0 2 (1 from prior hour + 1 new – 0 died = 2 remaining)
Srv1 Oct1 02:00 1 1 2 (2 from prior hour + 1 new – 1 died = 2 remaining)
Srv1 Oct1 03:00 1 0 3 (2 from prior hour + 1 new – 0 died = 3 remaining)
Srv1 Oct1 04:00 1 0 3 (3 from prior hour + 1 new –1 died = 3 remaining)
Srv1 Oct1 05:00 1 0 4 (3 from prior hour + 1 new – 0 died = 4 remaining)
Try this:
data have;
input Server $ PID Date $ Proc_Start_hour Proc_End_hour;
cards;
Srv1 1 Oct1 0 9
Srv1 2 Oct1 1 2
Srv1 3 Oct1 2 23
Srv1 4 Oct1 3 11
Srv1 5 Oct1 4 4
Srv1 6 Oct1 5 22
;
proc sql;
create table temp as
select Server, Date, t*3600 as Time format=time5.,
sum(a.t=b.proc_start_hour) as new label='+NewZombies',
sum(a.t=b.proc_end_hour) as died label='-ZombiesDied'
from (select proc_start_hour as t from have
union
select proc_end_hour as t from have) a,
have b
where a.t=b.proc_start_hour | a.t=b.proc_end_hour
group by Server, Date, Time
order by Server, Date, Time;
quit;
data want;
set temp;
by Server Date Time;
if first.date then total=0;
total+(new-died);
label total='TotalZombies';
run;
proc print data=want label;
run;
Please note that for Time=04:00 one zombie died.
This solution should work also for several servers and dates (where the dates would not be sorted in chronological order, though).
Thank YOU Soooooooooo Much!!! It worked like a charm! 🙂
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: