topic Re: Output first record of each member in SAS Programming

Output first record of each member

bibbnd — Wed, 21 Apr 2021 23:46:07 GMT

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.

Re: Output first record of each member

ballardw — Thu, 22 Apr 2021 00:47:31 GMT

@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.

Re: Output first record of each member

Astounding — Thu, 22 Apr 2021 00:53:19 GMT

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.

Re: Output first record of each member

japelin — Thu, 22 Apr 2021 02:53:59 GMT

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

if first.contract and last.contract then output;

if last.contract then output;

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;

Re: Output first record of each member

bibbnd — Thu, 22 Apr 2021 05:28:26 GMT

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

Re: Output first record of each member

Kurt_Bremser — Thu, 22 Apr 2021 08:45:02 GMT

@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.