dhms function, case + if then else or when

Syntax

DHMS(date,hour,minute,second)

Arguments

date specifies a SAS expression that represents a SAS date value.

hour is numeric.

minute is numeric.

second is numeric.

Details

The DHMS function returns a numeric value that represents a SAS datetime value. This numeric value can be either positive or negative.

example:

dtid=dhms('01jan03'd,15,30,15);
put dtid;
put dtid datetime.;

result:
1357054215
01JAN03:15:30:15
01JAN03:15:30:15

CASE when  then 
   when  then 
   else 
END

example:
data all;
input pat num date datetime19.;
cards;
12 1 27AUG2008:00:00:00
12 2 19SEP2008:00:00:00
12 3 12AUG2009:00:00:00
12 4 28AUG2009:00:00:00
12 5 09SEP2009:00:00:00
;
run;

proc print;
format date datetime19.;

proc sql;
   create table aa as
   select pat
         ,6 as ord
         ,min(case when num=4 and date ne . then dhms(mdy(month(datepart(date)),day(datepart(date)+7),year(datepart(date))),
              23, 59, 00) else . end) as stdt format=datetime19.
         ,min(case when num=5 and date ne . then dhms(mdy(month(datepart(date)),day(datepart(date)+7),year(datepart(date))),
              23, 59, 00) else . end) as spdt format=datetime19.
   from all(where=(num in(4,5)))
   group by pat
;
proc print data=aa;run;

Macro Statement: %DO %WHILE statement and %eval function

Syntax:
%do %while (expression);
text and macro program statements;
%end;

These examples illustrate expressions for the %DO %WHILE statement:

• %do %while(&a<&b);

• %do %while(%length(&name)>20);

Syntax:
%eval (arithmetic or logical expression)
evaluates integer arithmetic (add, minus, multiple and fraction)or logical expressions.
examples:
%let d=%eval(10+20); /* Correct usage */
example for %do %while %end and %eval


%macro see(str=) ;
%local i ;
%let i = 1 ;

%do %while ( %scan(&str.,&i.) ne ) ;

%put %scan(&str.,&i.) ;

%let i = %eval ( &i + 1 ) ;
%end ;

%mend see ;

%see ( str = aa bb cc )

data need;
do i=1 by 1 while (scan("a b c",i,"") ne "");
x=scan("a b c",i,"");
output;
end;
run;

data step and macro see do the same job.

_n_ and proc transpose

we have one data set like this:

Obs	list	status	var	relief
1	a	60	100	20
2	b	60	100	20
3	a	60	100	30
4	b	60	100	30

want to get a new data set like this:

Obs	status	var	relief	relief1	relief2
1	60	100	20	a	b
2	60	100	30	a	b

data have;
length list $1;
input status var relief list;
cards;
60 100 20 a
60 100 20 b
60 100 30 a
60 100 30 b
;

data have;
set have;
if _n_ eq 1 then id=1; /*_n_ calculates the occurrence of list*/
else if mod(_n_,2) then id+1;
run;

proc transpose data=have out=want (drop=id _:
rename=(col1=relief1
col2=relief2));
by id status var relief;
var list;
run;

FUNCTION MONOTONIC( ) IN PROC SQL

The automatic variable _N_ in DATA step processing counts the number of times the DATA step begins to iterate. It’s very useful when you need the iteration number from the DATA step.

Since PROC SQL uses a relational database concept that is different from the DATA step, we can’t get the iteration number from the PROC SQL procedure. An undocumented function, MONOTONIC( ), in PROC SQL that can generate very similar result as the _N_ in DATA step. Look at the following example:
Example 1:
proc sql;
select monotonic() as rowno, *
from testdata2
where monotonic() le 10;
quit;
The above program will generate the output:
rowno id y
-----------------------------
1 1 66
2 2 32
3 3 10
4 4 24
5 5 50
6 6 73
7 7 40
8 8 45
9 9 88
10 10 65

So we can treat the MONOTONIC( ) function in PROC SQL as the _N_ in DATA step if we need to use the row number of the table in PROC SQL.

data one;
input region $ vendorname $ amount carname $;
cards;
east a 2 kk
east a 2 tt
east a 2 ss
east a 2 ta
east a 2 lk
east a 2 oo
east a 2 pp
west b 4 op
west b 4 xp
west b 4 wp
west b 4 up
west b 4 lp
west b 4 oi
south n 9 mc
south n 9 ci
;
proc sql;
create table temp as
select region, count (*) as count
from one
group by region
order by region desc;
proc sql;
create table temp2 as
select * from temp
where monotonic() <=2;
quit;
(monotonic() give the observation number you want, where monotonic()<=20 chooses the first 20 observations from table temp)