topic Re: How to assign macro to the values in the first and the last row of the table? in SAS Programming

How to assign macro to the values in the first and the last row of the table?

sachin01663 — Thu, 14 Feb 2013 15:18:02 GMT

Hi,

I am doing this in a length way so wondering is there is anything simpler - How do I keep only first row and the last row in a table? I need to assign a macro to the date value in the first row and another macro to the date value in last row of the Date variable. Example:

Name	Response	Date
A	10	01-Jan-13
B	20	02-Jan-13
C	30	02-Jan-13
A	40	03-Jan-13
C	40	03-Jan-13

So I need two macros: Start_date which should have 01-JAN-13 (whatever is the date value in the first row) and End_Date 03-Jan-13 (whatever is the date value in the last row).

My Approach:

proc sql;

select count(*) into :last_obs from Old where not missing(response_date);

quit;

Data New;

set old;

if not missing (response_date) then Days+1;

run;

Data new2;

set new;

if not missing (response_date);

if days = 1 then call symput ('Start_Date',response_date);

if days=&last_obs then call symput ('End_Date',response_date);

drop days;

run;

%put &start_date; %put &End_Date;

endrsubmit;

Thanks as always.

Re: How to assign macro to the values in the first and the last row of the table?

PaigeMiller — Thu, 14 Feb 2013 15:28:19 GMT

Simple is in the eye of the beholder. What you have should work, so I'm not sure what the problem is.

But here's my attempt ... (and by the way, you are assigning these date values to macro variables, not to macros as you state)

data _null_;

set old end=eof;

if _n_=1 then call symputx('start_date',response_date);

if eof then call symputx('end_date',response_date);

run;

Re: How to assign macro to the values in the first and the last row of the table?

Haikuo — Thu, 14 Feb 2013 15:40:06 GMT

You probably need two steps if you don't want any missing values in macro variables.

/*first, make sure no missing values*/

Data new;

set old;

if not missing (response_date);

run;

/*The second step only reads two obs, so it shouldn't slow you down much*/

data _null_;

do n=1,nobs;

set new nobs=nobs point=n;

if n=1 then call symputx('start_date',response_date);

if n=nobs then call symputx('end_date',response_date);

end;

stop;

run;

Haikuo

Re: How to assign macro to the values in the first and the last row of the table?

willhunt — Thu, 14 Feb 2013 16:51:37 GMT

I'll add 2 "but if"s to Paige's post.

If you are concerned about missing values in response_date:

data _null_;

set old(where=(response_date ne .)) end=eof;

if _n_=1 then call symputx('start_date',response_date);

if eof then call symputx('end_date',response_date);

run;

If you want the chronological start and end dates, and aren't sure that your dataset is sorted:

proc summary data=old ;

var response_date ;

output out=new min=day1 max=day2 ;

quit;

data _null_ ;

set new ;

call symputx('start_date',day1) ;

call symputx('end_date,day2) ;

run;

Re: How to assign macro to the values in the first and the last row of the table?

DBailey — Thu, 14 Feb 2013 17:07:15 GMT

Assuming you really just want the min/max non-missing dates:

proc sql noprint;

select

min(response_date) format=best20. as MinResponse,

max(response_date) format=best20. as MaxResponse

into

:Start_Date

,:End_Date

from have

where response_date is not null;

quit;

Re: How to assign macro to the values in the first and the last row of the table?

optimist — Fri, 15 Feb 2013 15:37:30 GMT

Without wondering too much about your end goal--or whether or not you even need to use a data step, rather than just peeking inside of the table, the following code should work. The table "old" is your original table.

/***********************************************************

***********************************************************/

data new2;

set old (where=(not missing (response_date))) end = my_last_obs;

if _N_ =1 then call symput ('Start_Date',response_date);

if my_last_obs then call symput ('End_Date',response_date);

run;

%put &start_date; %put &End_Date;

/***********************************************************

***********************************************************/

Re: How to assign macro to the values in the first and the last row of the table?

sachin01663 — Sun, 17 Feb 2013 14:50:19 GMT

Thank you. That's simpler than what I wrote.