DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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


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:

NameResponseDate
A1001-Jan-13
B2002-Jan-13
C3002-Jan-13
A4003-Jan-13
C4003-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.


Accepted Solutions
Solution
‎02-14-2013 10:28 AM
Trusted Advisor
Posts: 1,932

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

Posted in reply to sachin01663

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;

View solution in original post


All Replies
Solution
‎02-14-2013 10:28 AM
Trusted Advisor
Posts: 1,932

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

Posted in reply to sachin01663

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;

New Contributor
Posts: 4

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

Posted in reply to PaigeMiller

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;

Contributor
Posts: 40

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

Posted in reply to PaigeMiller

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

Respected Advisor
Posts: 3,156

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

Posted in reply to sachin01663

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

Super Contributor
Posts: 578

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

Posted in reply to sachin01663

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

     Smiley Frustratedtart_Date

     ,:End_Date

from have

where response_date is not null;

quit;

Occasional Contributor
Posts: 6

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

Posted in reply to sachin01663

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;

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 518 views
  • 8 likes
  • 6 in conversation