BookmarkSubscribeRSS Feed
bibbnd
Fluorite | Level 6

I want the first record of each member only.

 

proc sort data=billfinal; by contract bill_end descend;

quit;

 

data billfinal2;

 set billfinal;

by contract bill_end;

if first.contract and last.contract then output;

run;

I get back an empty dataset;

 

So I  removed the last.contract and than i only get 1 row of data. 

 

i am not sure what i am doing wrong to get the first record of each different contract.  a contract can have multiple rows due to the bill date but i only want the last one. 

5 REPLIES 5
ballardw
Super User

@bibbnd wrote:

I want the first record of each member only.

 

proc sort data=billfinal; by contract bill_end descend;

quit;

 

data billfinal2;

 set billfinal;

by contract bill_end;

if first.contract and last.contract then output;

run;

I get back an empty dataset;

 

So I  removed the last.contract and than i only get 1 row of data. 

 

i am not sure what i am doing wrong to get the first record of each different contract.  a contract can have multiple rows due to the bill date but i only want the last one. 


So why do you include "First.contract"? First and last for a single variable are true when there is only one value of a by variable.

 

Did you try " if last.contract then output;" ?

 

You may also need to check your sort order. What does your log show? It is very possible that your sort failed unless you have a variable named "descend" in the data if that is the sort code you showed.

 

 

Astounding
PROC Star

The very best programmers in the world will always look at the log to see what went wrong.  If you want help, you should post the log.

bibbnd
Fluorite | Level 6
there is no log. it just output the first row of the dataset and not the other records
Kurt_Bremser
Super User

@bibbnd wrote:
there is no log. it just output the first row of the dataset and not the other records

There is ALWAYS a log unless SAS has crashed or nothing was submitted at all.

 

See this example code:

data have;
input contract $ bill_end :yymmdd10.;
format bill_end yymmdd10.;
datalines;
A 2020-02-03
A 2021-01-01
B 2021-01-01
B 2021-02-01
;

proc sort data=have;
by contract descending bill_end;
run;

data want;
set have;
by contract;
if first.contract;
run;

and the log from it when it is submitted:

 73         data have;
 74         input contract $ bill_end :yymmdd10.;
 75         format bill_end yymmdd10.;
 76         datalines;
 
 NOTE: The data set WORK.HAVE has 4 observations and 2 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 81         ;
 82         
 83         proc sort data=have;
 84         by contract descending bill_end;
 85         run;
 
 NOTE: There were 4 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.HAVE has 4 observations and 2 variables.
 NOTE:  Verwendet wurde: PROZEDUR SORT - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 86         
 87         data want;
 88         set have;
 89         by contract;
 90         if first.contract;
 91         run;
 
 NOTE: There were 4 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.WANT has 2 observations and 2 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds

For further help, provide example data in a data step with datalines (see above, do not skip this!), so we can provide code that works with the data as posted.

 

 

japelin
Rhodochrosite | Level 12

If you only want the last one row, you can change this.

if first.contract and last.contract then output;

to

if last.contract then output;

or

if last.contract;

 

 

As for the sort order, as @ballardw already mentioned, check if descend is a variable or if it is intended for descending order.
If you want to sort in descending order, put "descending" before the variable name.

 

proc sort data=billfinal;
  by contract descending bill_end;
run;

 

 

If you want everything to be in descending order, you can use the key statement.

 

proc sort data=billfinal; 
  key contract descending / descending;
run;

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

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
  • 5 replies
  • 1728 views
  • 0 likes
  • 5 in conversation